<?php

namespace App\V3\V3_2_7\Controllers;

use App\Helper\DBRSAUtil;
use App\Http\Controllers\Controller;
use App\Http\Requests\V3_2_7\VerifyIdRequest;
use App\V3\V3_2_7\Models\BarterGoodsDetailModel;
use App\V3\V3_2_7\Models\CustomerServiceModel;
use App\V3\V3_2_7\Models\CustomTagModel;
use App\V3\V3_2_7\Models\MaterielContentModel;
use App\V3\V3_2_7\Models\MaterielModel;
use App\V3\V3_2_7\Models\OutAnimalTagModel;
use App\V3\V3_2_7\Models\ProductOrderModel;
use App\V3\V3_2_7\Models\SalesReturnModel;
use App\V3\V3_2_7\Models\SellGoodsDetail;
use App\V3\V3_2_7\Models\SellModel;
use App\V3\V3_2_7\Models\SmPhotoAlbumModel;
use App\V3\V3_2_7\Models\StorageShelfLocationModel;
use App\V3\V3_2_7\Models\StorageShelfModel;
use App\V3\V3_2_7\Models\SupplierCustomerModel;
use App\V3\V3_2_7\Models\SystemConfigModel;
use App\V3\V3_2_7\Models\AdminModel;
use App\V3\V3_2_7\Services\ApplicationFormService;
use App\V3\V3_2_7\Services\CommonStorageOperateService;
use App\V3\V3_2_7\Services\LocationLogService;
use App\V3\V3_2_7\Services\LogService;
use App\V3\V3_2_7\Services\MaterielsService;
use App\V3\V3_2_7\Services\ShelfLocationTagService;
use App\V3\V3_2_7\Services\StorageManageService;
use App\V3\V3_2_7\Services\StorageShelfLocationService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Validator;
use App\V3\V3_2_7\Services\SellsService;
use Illuminate\Validation\Rule;
use OSS\OssClient;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\V3\V3_2_7\Models\SerialNumberModel;
use App\V3\V3_2_7\Services\SalesReturnsService;
use App\V3\V3_2_7\Services\StatisticsService;
use App\V3\V3_2_7\Models\TallyModel;
use App\V3\V3_2_7\Services\OverflowBreakageService;
use App\V3\V3_2_7\Models\AdminAddendumModel;
use App\V3\V3_2_7\Models\MaterielTypeModel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\V3\V3_2_7\Models\MaterielClassModel;
use App\V3\V3_2_7\Models\TallyClassModel;
use App\V3\V3_2_7\Models\StorageModel;
use App\V3\V3_2_7\Models\StorageManageModel;
use App\V3\V3_2_7\Models\LogInStorageModel;
use App\V3\V3_2_7\Models\LogOutStorageModel;
use App\V3\V3_2_7\Services\CheckStorageManageService;
use App\V3\V3_2_7\Services\MaterielClassService;
use App\V3\V3_2_7\Models\MaterielProductContentModel;
use Illuminate\Support\Arr;
use Illuminate\Support\Carbon;
use Illuminate\Support\Str;
use App\Services\CommonService;
use App\V3\V3_2_7\Services\BarterService;
use App\V3\V3_2_7\Models\BarterLogisticsInfoModel;
use App\V3\V3_2_7\Models\SalesReturnLogisticsInfoModel;
use App\V3\V3_2_7\Models\BarterOutSerialNumberModel;
use App\V3\V3_2_7\Models\SalesOutSerialNumberModel;
use App\V3\V3_2_7\Services\LoginService;

class CommonsController extends Controller
{
    protected $ossClient;

    public function __construct(Request $request)
    {

        $this->ossClient = new OssClient(
            config('app.aliyun_oss_key'),
            config('app.aliyun_oss_secret'),
            config('app.aliyun_oss_endpoint')
        );
    }

    public function readAddress(Request $request)
    {
        if (!empty($content = $request->input('content'))) {
            $newContent = preg_replace('/[^\x{4e00}-\x{9fa5}a-zA-Z0-9]/u', " ", $content);

            $contentArr = array_filter(explode(' ', $newContent), function ($v) {
                return $v !== '';
            });

            if (empty($contentArr[0])) {
                return response()->json(
                    ['code' => 1, 'msg' => '姓名不能为空', 'data' => '']
                );
            }

            $data['name'] = $contentArr[0];
            $data['mobile'] = empty($contentArr[1]) ? '' : $contentArr[1];
            $data['address'] = empty($contentArr[2]) ? '' : $contentArr[2];

            if (!empty($contentArr[1])) {
                $checkMobileInfo = self::checkMobile($data);

                if (!empty($checkMobileInfo)) {
                    return response()->json([
                        'code' => 1,
                        'msg' => $checkMobileInfo,
                        'data' => ''
                    ]);
                }
            }

            return response()->json(
                ['code' => 0, 'msg' => 'success', 'data' => $data]
            );
        } else {
            return response()->json(
                ['code' => 0, 'msg' => 'success', 'data' => '']
            );
        }
    }

    /**
     * 查询物料商品信息（zlf）
     * @param goods_id 物料商品
     */
    public function getGoodsInfo(Request $request)
    {
        $messages = [
            'goods_id.required' => '商品id,不能为空',
            'goods_id.integer' => '商品id,为整数',
        ];

        $validator = Validator::make($data = $request->all(), [
            'goods_id' => 'required|integer',
        ], $messages);

        $msg = $validator->errors()->first();

        if (!empty($msg)) return $msg;
        $info = MaterielTypeModel::where($data)->where('delete_flag', 0)->frist();
        return response()->json(['code' => 0, 'success', 'data' => $info]);
    }

    private function checkMobile($data)
    {
        $messages = [
            'mobile.required' => '手机号不能为空，请输入手机号',
            'mobile.regex' => '手机号格式错误，请重试',
        ];

        $validator = Validator::make($data, [
            'mobile' => 'required|regex:/^1[3456789][0-9]{9}$/',
        ], $messages);

        return $validator->errors()->first();
    }

    /**
     * 导出excel-销售记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/10 10:48
     * @return array|void
     */
    public function sellExport(Request $request)
    {
        $data = $request->all();
        if (empty($data['token'])) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        /* $token = $request->header('token'); */
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        /*   $tokenInfo['mainUserId'] = 6588;
         $tokenInfo['userId'] = 6588;
         $data['materiel_class_id'] = 44801; */
        $array = explode(' ', $data['token']);

        //获取导出的数据
        $exportData = SellsService::exportData($tokenInfo['userId'], $data);
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('销售记录表');
        $spreadsheet = self::spreadsheetCommonMethod($spreadsheet, "A", "AJ");
        $sheet->setCellValue('A1', '日期')
            ->setCellValue('B1', '序列')
            ->setCellValue('C1', '操作人昵称')
            ->setCellValue('D1', '操作人账号')
            ->setCellValue('E1', '收货人')
            ->setCellValue('F1', '订单编号')
            ->setCellValue('G1', '出库状态')
            ->setCellValue('H1', '付款状态')
            ->setCellValue('I1', '备注')
            ->setCellValue('J1', '销售时间')
            ->setCellValue('K1', '单位')
            ->setCellValue('L1', '渠道')
            ->setCellValue('M1', '商品类型')
            ->setCellValue('N1', '类别')
            ->setCellValue('O1', '名称/规格(编号)')
            ->setCellValue('P1', '数量')
            ->setCellValue('Q1', '销售价（元）')
            ->setCellValue('R1', '成本价（元）')
            ->setCellValue('S1', '商品单位')
            ->setCellValue('T1', '仓库名称')
            ->setCellValue('U1', '序列号')
            ->setCellValue('V1', '折扣 ：百分比')
            ->setCellValue('W1', '税率 ：百分比')
            ->setCellValue('X1', '货款金额（元）')
            ->setCellValue('Y1', '其他费用（元）')
            ->setCellValue('Z1', '实付金额（元）')
            ->setCellValue('AA1', '总计金额（元）')
            ->setCellValue('AB1', '付款公司名称')
            ->setCellValue('AC1', '发货方式')
            ->setCellValue('AD1', '运输方式')
            ->setCellValue('AE1', '物流公司')
            ->setCellValue('AF1', '物流单号')
            ->setCellValue('AG1', '结算重量')
            ->setCellValue('AH1', '计价重量')
            ->setCellValue('AI1', '运费（元）')
            ->setCellValue('AJ1', '件数');
        $goodsNum = [];
        foreach ($exportData as $key => $value) {
            if (!empty($value['materiel_info'])) {//商品信息
                $goodsNum[] = count($value['materiel_info']);
                if (empty($value['out_status'])) {
                    $out_status_msg = '待出库';
                } elseif ($value['out_status'] == 1) {
                    $out_status_msg = '部分出库';
                } elseif ($value['out_status'] == 2) {
                    $out_status_msg = '全部出库';
                }

                if (empty($value['pay_status'])) {
                    $pay_status_msg = '待付款';
                } elseif ($value['pay_status'] == 1) {
                    $pay_status_msg = '部分付款';
                } elseif ($value['pay_status'] == 2) {
                    $pay_status_msg = '全部付款';
                }

                if ($value['deliver_goods_type'] == 1) {
                    $deliver_goods_type_msg = '自提';
                } elseif ($value['deliver_goods_type'] == 2) {
                    $deliver_goods_type_msg = '代发';
                } elseif ($value['deliver_goods_type'] == 3) {
                    $deliver_goods_type_msg = '送货';
                } else {
                    $deliver_goods_type_msg = '';
                }

                if ($value['transport_type'] == 1) {
                    $transport_type_msg = '空运';
                } elseif ($value['transport_type'] == 2) {
                    $transport_type_msg = '海运';
                } elseif ($value['transport_type'] == 3) {
                    $transport_type_msg = '铁路';
                } elseif ($value['transport_type'] == 4) {
                    $transport_type_msg = '公路';
                } else {
                    $transport_type_msg = '';
                }

                foreach ($value['materiel_info'] as $k => $item) {
                    //销售商品

                    $condition = [];
                    $condition['type_id'] = $item['materiel_type_id'];
                    $condition['b.sell_id'] = $item['sell_id'];
                    $condition['b.main_id'] = $value['main_id'];

                    $goodsSerialList = SerialNumberModel::select('serial_no')
                        ->from('serial_number as a')
                        ->leftJoin('sell_out_serial_numbers as b', 'b.serial_number', '=', 'a.serial_no')
                        ->where($condition)
                        ->where([
                            'a.status' => 0,
                            'a.delete_flag' => 0
                        ])->get()->toArray();

                    if (!empty($goodsSerialList)) $goodsSerialList = implode(',', array_map('array_shift', $goodsSerialList));

                    /*   if($item['is_product_materiel'] == 1){
                        $type = '套餐商品';
                    }else{
                        $type = '普通商品';
                    } */

                    $mInfo = MaterielClassModel::where(['id' => $item['materiel_class_id']])->first();

                    $list[] = [
                        'o_date' => date('m', time()) . '月' . date('d', time()) . '号',//日期
                        'nickname' => $value['nickname'],//操作人昵称
                        'username' => $value['username'],//操作人账号
                        'consignee' => $value['consignee'] . ',' . $value['phone'] . ',' . $value['address'],//收货人
                        'order_no' => $value['order_no'], //订单编号
                        'out_status' => $out_status_msg,//出库状态
                        'pay_status' => $pay_status_msg,//付款状态
                        'remark' => $value['remark'],//备注
                        'sell_date' => $value['sell_date'],//销售时间
                        'unit' => '睡猫云仓',//单位
                        'channel' => '睡猫云仓', //渠道
                        'goods_type' => "普通商品",//商品类型
                        'goods_class' => $mInfo['class_name'] ?? '',//类别
                        'goods_info' => $item['name'] . '/' . $item['type_name'] . "（" . $item['materiel_no'] . "）",//规格
                        'num' => $item['num'],//数量
                        'price' => $item['price'],//销售价
                        'cost_price' => $item['cost_price'],//成本价
                        'piece' => $item['piece'],//单位
                        'warehouse' => $item['warehouse'] . ',' . $item['shelf_name'] . ',' . $item['location_name'],//仓库
                        'serial_list' => !empty($goodsSerialList) ? $goodsSerialList : "",//序列号
                        'discount' => $value['discount'],//折扣 ：百分比
                        'tax_rate' => $value['tax_rate'],//税率 ：百分比
                        'money' => $value['money'],//货款金额（元）
                        'other_charges' => $value['other_charges'],//其他费用（元）
                        'real_money' => $value['real_money'],   //实付金额
                        'final_money' => $value['final_money'],//总计金额
                        'paying_company' => "",//付款公司名称
                        'deliver_goods_type' => $deliver_goods_type_msg,//发货方式
                        'transport_type' => $transport_type_msg,//运输方式
                        'logistics_company' => $value['logistics_company'],//物流公司
                        'logistics_order_no' => $value['logistics_order_no'], //物流单号
                        'settlement_weight' => "",//结算重量
                        'valuation_weight' => "",//计价重量
                        'freight' => "",//运费
                        'number_cases' => "",//件数
                    ];

                }
            }
        }

        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ'];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
        }

        if (!empty($list)) {
            $i = 1;
            foreach ($list as $key => $value) {

                if ($key > 0) {
                    if ($list[$key - 1]['order_no'] != $value['order_no']) {
                        $i++;
                    }
                }

                $key += 2;
                $sheet->setCellValue('A' . $key, $value['o_date'])
                    ->setCellValue('B' . $key, $i)
                    ->setCellValue('C' . $key, $value['nickname'])
                    ->setCellValue('D' . $key, $value['username'])
                    ->setCellValue('E' . $key, $value['consignee'])
                    ->setCellValue('F' . $key, $value['order_no'])
                    ->setCellValue('G' . $key, $value['out_status'])
                    ->setCellValue('H' . $key, $value['pay_status'])
                    ->setCellValue('I' . $key, $value['remark'])
                    ->setCellValue('J' . $key, $value['sell_date'])
                    ->setCellValue('K' . $key, $value['unit'])
                    ->setCellValue('L' . $key, $value['channel'])
                    ->setCellValue('M' . $key, $value['goods_type'])
                    ->setCellValue('N' . $key, $value['goods_class'])
                    ->setCellValue('O' . $key, $value['goods_info'])
                    ->setCellValue('P' . $key, $value['num'])
                    ->setCellValue('Q' . $key, $value['price'])
                    ->setCellValue('R' . $key, $value['cost_price'])
                    ->setCellValue('S' . $key, $value['piece'])
                    ->setCellValue('T' . $key, $value['warehouse'])
                    ->setCellValue('U' . $key, $value['serial_list'])
                    ->setCellValue('V' . $key, $value['discount'])
                    ->setCellValue('W' . $key, $value['tax_rate'])
                    ->setCellValue('X' . $key, $value['money'])
                    ->setCellValue('Y' . $key, $value['other_charges'])
                    ->setCellValue('Z' . $key, $value['real_money'])
                    ->setCellValue('AA' . $key, $value['final_money'])
                    ->setCellValue('AB' . $key, $value['paying_company'])
                    ->setCellValue('AC' . $key, $value['deliver_goods_type'])
                    ->setCellValue('AD' . $key, $value['transport_type'])
                    ->setCellValue('AE' . $key, $value['logistics_company'])
                    ->setCellValue('AF' . $key, $value['logistics_order_no'])
                    ->setCellValue('AG' . $key, $value['settlement_weight'])
                    ->setCellValue('AH' . $key, $value['valuation_weight'])
                    ->setCellValue('AI' . $key, $value['freight'])
                    ->setCellValue('AJ' . $key, $value['number_cases']);

            }

        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('销售记录表', $data, '导出共' . count($exportData) . '条');

        return self::downloadExcel($spreadsheet, '销售记录表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-退货记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/04 14:04
     * @return array|void
     */
    public function salesReturnExport(Request $request)
    {
        $data = $request->all();
        if (empty($data['token'])) {

            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        //$array = explode(' ', $data['token']);
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'search_type' => [
                'required',
                Rule::In([1, 2, 3, 4, 5, 6, 7, 8])
            ],
            'status' => [
                'required',
                Rule::In([0, 1, 2, 3, 4, 5, 6])
            ],
            'repeal_status' => [
                'required',
                Rule::In([0, 1])
            ]
        ], [
            'search_type.required' => '参数`搜索类型`缺失',
            'search_type.in' => '参数`搜索类型`不合法',
            'status.required' => '参数`状态`缺失',
            'status.in' => '参数`状态`不合法',
            'repeal_status.required' => '参数`撤销状态`缺失',
            'repeal_status.in' => '参数`撤销状态`不合法',
        ]);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => '',
            ]);
        }

        if (isset($request->start_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'start_time' => 'date_format:Y-m-d H:i:s',//页面
            ], [
                'start_time.date_format' => '开始时间格式错误，例：' . date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json([
                    'code' => 1,
                    'msg' => $infos,
                    'data' => '',
                ]);
            }
        }

        if (isset($request->end_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'end_time' => 'date_format:Y-m-d H:i:s',//页面
            ], [
                'end_time.date_format' => '结束时间格式错误，例：' . date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json([
                    'code' => 1,
                    'msg' => $infos,
                    'data' => '',
                ]);
            }
        }

        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();

        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);

        if ($adminInfo['parent_id'] != 0) {

            $data['user_id'] = $tokenInfo['userId'];
            $data['main_id'] = $adminInfo['parent_id'];
        } else {
            $data['user_id'] = $tokenInfo['userId'];
            $data['main_id'] = $tokenInfo['userId'];
        }

        if ($tokenInfo['userId'] == $adminInfo['parent_id']) {
            $userId['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        } else {
            $userId['a.user_id'] = ['EQ' => $tokenInfo['userId']];
        }
        //获取导出的数据


        $exportData = SalesReturnsService::exportData($userId, $data);

        /*  echo "<pre>";
        var_dump($exportData);die; */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('退货记录');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "AS");
        $sheet->setCellValue('A1', '订单号')
            ->setCellValue('B1', '操作人昵称')
            ->setCellValue('C1', '操作人账号')
            ->setCellValue('D1', '客户姓名')
            ->setCellValue('E1', '收货人')
            ->setCellValue('F1', '联系电话')
            ->setCellValue('G1', '收货地址')
            ->setCellValue('H1', '折扣 ：百分比')
            ->setCellValue('I1', '税率 ：百分比')
            ->setCellValue('J1', '实际应收金额（元）')
            ->setCellValue('K1', '实收金额（元）')
            ->setCellValue('L1', '货款金额（元）')
            ->setCellValue('M1', '备注')
            ->setCellValue('N1', '入库状态')
            ->setCellValue('O1', '付款状态')
            ->setCellValue('P1', '退货时间')
            ->setCellValue('Q1', '商品信息')
            ->setCellValue('R1', '商品名称')
            ->setCellValue('S1', '商品型号')
            ->setCellValue('T1', '销售价（元）')
            ->setCellValue('U1', '商品成本（元）')
            ->setCellValue('V1', '商品数量')
            ->setCellValue('W1', '入库数量')
            ->setCellValue('X1', '在库数量')
            ->setCellValue('Y1', '仓库名称')
            ->setCellValue('Z1', '货架名称')
            ->setCellValue('AA1', '库位名称')
            ->setCellValue('AB1', '序列号')
            ->setCellValue('AC1', '批次号')
            ->setCellValue('AD1', '套餐信息')
            ->setCellValue('AE1', '套餐名称')
            ->setCellValue('AF1', '售价（元）')
            ->setCellValue('AG1', '套餐总成本（元）')
            ->setCellValue('AH1', '套餐数量')
            ->setCellValue('AI1', '已入库套餐数量')
            ->setCellValue('AJ1', '商品名称')
            ->setCellValue('AK1', '规格名称')
            ->setCellValue('AL1', '数量')
            ->setCellValue('AM1', '在库数量')
            ->setCellValue('AN1', '仓库名称')
            ->setCellValue('AO1', '仓库地址')
            ->setCellValue('AP1', '货架名称')
            ->setCellValue('AQ1', '库位名称')
            ->setCellValue('AR1', '序列号')
            ->setCellValue('AS1', '批次号');
        $goodsNum = [];
        foreach ($exportData as $key => $value) {
            if (empty($value['out_status'])) {
                $out_status_msg = '待入库';
            } elseif ($value['out_status'] == 1) {
                $out_status_msg = '部分入库';
            } elseif ($value['out_status'] == 2) {
                $out_status_msg = '全部入库';
            }

            if (empty($value['pay_status'])) {
                $pay_status_msg = '待付款';
            } elseif ($value['pay_status'] == 1) {
                $pay_status_msg = '部分付款';
            } elseif ($value['pay_status'] == 2) {
                $pay_status_msg = '全部付款';
            }

            if (!empty($value['return_materiel']) && empty($value['return_package'])) {//只有退货商品

                $goodsSerialList = "";
                $goodsBatchList = "";
                $goodsNum[] = count($value['return_materiel']);
                foreach ($value['return_materiel'] as $item) {

                    if (!empty($item['goods_serial_info'])) {//序列号

                        foreach ($item['goods_serial_info'] as $gk => $gv) {

                            $goodsSerialList .= $gv['serial_number'] . ',';
                        }

                        $goodsSerialList = substr($goodsSerialList, 0, strrpos($goodsSerialList, ","));
                    }

                    if (!empty($item['goods_batch_info'])) {//批次号

                        foreach ($item['goods_batch_info'] as $bk => $bv) {

                            $goodsBatchList .= $bv['batch_no'] . ',';
                        }

                        $goodsBatchList = substr($goodsBatchList, 0, strrpos($goodsBatchList, ","));
                    }
                    $list[] = [
                        'order_no' => $value['order_no'],
                        'nickname' => $value['nickname'],
                        'username' => $value['username'],
                        'customer_name' => $value['customer_name'],
                        'consignee' => $value['consignee'],
                        'phone' => $value['phone'],
                        'address' => $value['address'],
                        'discount' => $value['discount'],
                        'tax_rate' => $value['tax_rate'],
                        'final_money' => $value['final_money'],
                        'real_money' => $value['real_money'],
                        'money' => $value['money'],
                        'remark' => $value['remark'],
                        'out_status' => $out_status_msg,
                        'pay_status' => $pay_status_msg,
                        'refund_date' => $value['refund_date'],
                        'goods_info' => '商品信息>>>',
                        'name' => $item['name'],
                        'type_name' => $item['type_name'],
                        'price' => $item['price'],
                        'cost_price' => $item['cost_price'],
                        'num' => $item['num'],
                        'in_num' => $item['in_num'],
                        'in_storage_num' => $item['in_storage_num'],
                        'warehouse' => $item['warehouse'],
                        'shelf_name' => $item['shelf_name'],
                        'location_name' => $item['location_name'],
                        'serial_list' => $goodsSerialList,
                        'batch_list' => $goodsBatchList,
                        'tc_info' => '套餐信息>>>',
                        'package_name' => "",
                        'tc_price' => "",
                        'tc_cost_price' => "",
                        'tc_num' => "",
                        'tc_in_num' => "",
                        'tc_materiel_name' => "",
                        'tc_type_name' => "",
                        'tc_goods_num' => "",
                        'tc_in_storage_num' => "",
                        'storage_name' => "",
                        'storage_address' => "",
                        'tc_shelf_name' => "",
                        'tc_location_name' => "",
                        'tc_serial_list' => "",
                        'tc_batch_list' => ""
                    ];
                }
            } elseif (!empty($value['return_materiel']) && !empty($value['return_package'])) {//退货商品和退货套餐都有

                foreach ($value['return_package'] as $pk => $pv) {

                    if (!empty($pv['package_content']) && count($value['return_materiel']) >= count($pv['package_content'])) {//退货商品数量大于等于套餐商品数量

                        $goodsNum[] = count($value['return_materiel']);

                        foreach ($value['return_materiel'] as $k => $item) {
                            $goodsSerialList = "";
                            $goodsBatchList = "";
                            $packageSerialList = "";
                            $packageBatchList = "";
                            //退货商品序列号和批次号
                            if (!empty($item['goods_serial_info'])) {//序列号

                                foreach ($item['goods_serial_info'] as $gk => $gv) {

                                    $goodsSerialList .= $gv['serial_number'] . ',';
                                }

                                $goodsSerialList = substr($goodsSerialList, 0, strrpos($goodsSerialList, ","));
                            }
                            if (!empty($item['goods_batch_info'])) {//批次号

                                foreach ($item['goods_batch_info'] as $bk => $bv) {

                                    $goodsBatchList .= $bv['batch_no'] . ',';
                                }

                                $goodsBatchList = substr($goodsBatchList, 0, strrpos($goodsBatchList, ","));
                            }

                            //退货套餐的序列号和批次号
                            if (!empty($pv['package_content'][$k]['package_serial_info'])) {//序列号

                                foreach ($pv['package_content'][$k]['package_serial_info'] as $sk => $sv) {

                                    $packageSerialList .= $sv['serial_number'] . ',';
                                }

                                $packageSerialList = substr($packageSerialList, 0, strrpos($packageSerialList, ","));

                            }
                            if (!empty($pv['package_content'][$k]['package_batch_info'])) {//批次号

                                foreach ($pv['package_content'][$k]['package_batch_info'] as $bk => $bv) {

                                    $packageBatchList .= $bv['batch_no'] . ',';
                                }

                                $packageBatchList = substr($packageBatchList, 0, strrpos($packageBatchList, ","));
                            }

                            $list[] = [
                                'order_no' => $value['order_no'],
                                'nickname' => $value['nickname'],
                                'username' => $value['username'],
                                'customer_name' => $value['customer_name'],
                                'consignee' => $value['consignee'],
                                'phone' => $value['phone'],
                                'address' => $value['address'],
                                'discount' => $value['discount'],
                                'tax_rate' => $value['tax_rate'],
                                'final_money' => $value['final_money'],
                                'real_money' => $value['real_money'],
                                'money' => $value['money'],
                                'remark' => $value['remark'],
                                'out_status' => $out_status_msg,
                                'pay_status' => $pay_status_msg,
                                'refund_date' => $value['refund_date'],
                                'goods_info' => '商品信息>>>',
                                'name' => $item['name'],
                                'type_name' => $item['type_name'],
                                'price' => $item['price'],
                                'cost_price' => $item['cost_price'],
                                'num' => $item['num'],
                                'in_num' => $item['in_num'],
                                'in_storage_num' => $item['in_storage_num'],
                                'warehouse' => $item['warehouse'],
                                'shelf_name' => $item['shelf_name'],
                                'location_name' => $item['location_name'],
                                'serial_list' => $goodsSerialList,
                                'batch_list' => $goodsBatchList,
                                'tc_info' => '套餐信息>>>',
                                'package_name' => !empty($pv['package_name']) ? $pv['package_name'] : "",
                                'tc_price' => !empty($pv['price']) ? $pv['price'] : "",
                                'tc_cost_price' => !empty($pv['cost_price']) ? $pv['cost_price'] : "",
                                'tc_num' => !empty($pv['num']) ? $pv['num'] : "",
                                'tc_in_num' => !empty($pv['in_num']) ? $pv['in_num'] : "",
                                'tc_materiel_name' => !empty($pv['package_content'][$k]['materiel_name']) ? $pv['package_content'][$k]['materiel_name'] : "",
                                'tc_type_name' => !empty($pv['package_content'][$k]['type_name']) ? $pv['package_content'][$k]['type_name'] : "",
                                'tc_goods_num' => !empty($pv['package_content'][$k]['num']) ? $pv['package_content'][$k]['num'] : "",
                                'tc_in_storage_num' => !empty($pv['package_content'][$k]['in_storage_num']) ? $pv['package_content'][$k]['in_storage_num'] : "",
                                'storage_name' => !empty($pv['package_content'][$k]['storage_name']) ? $pv['package_content'][$k]['storage_name'] : "",
                                'storage_address' => !empty($pv['package_content'][$k]['storage_address']) ? $pv['package_content'][$k]['storage_address'] : "",
                                'tc_shelf_name' => !empty($pv['package_content'][$k]['shelf_name']) ? $pv['package_content'][$k]['shelf_name'] : "",
                                'tc_location_name' => !empty($pv['package_content'][$k]['location_name']) ? $pv['package_content'][$k]['location_name'] : "",
                                'tc_serial_list' => !empty($packageSerialList) ? $packageSerialList : "",
                                'tc_batch_list' => !empty($packageBatchList) ? $packageBatchList : ""
                            ];
                        }
                    } else {
                        //退货商品数量小于套餐商品数量
                        $goodsNum[] = count($pv['package_content']);

                        foreach ($pv['package_content'] as $pck => $pcv) {

                            $goodsSerialList = "";
                            $goodsBatchList = "";
                            $packageSerialList = "";
                            $packageBatchList = "";

                            //退货商品序列号和批次号
                            if (!empty($value['return_materiel'][$pck]['goods_serial_info'])) {//序列号

                                foreach ($value['return_materiel'][$pck]['goods_serial_info'] as $gk => $gv) {

                                    $goodsSerialList .= $gv['serial_number'] . ',';
                                }

                                $goodsSerialList = substr($goodsSerialList, 0, strrpos($goodsSerialList, ","));
                            }
                            if (!empty($value['return_materiel'][$pck]['goods_batch_info'])) {//批次号

                                foreach ($value['return_materiel'][$pck]['goods_batch_info'] as $bk => $bv) {

                                    $goodsBatchList .= $bv['batch_no'] . ',';
                                }

                                $goodsBatchList = substr($goodsBatchList, 0, strrpos($goodsBatchList, ","));
                            }

                            //退货套餐的序列号和批次号
                            if (!empty($pcv['package_serial_info'])) {//序列号

                                foreach ($pcv['package_serial_info'] as $sk => $sv) {

                                    $packageSerialList .= $sv['serial_number'] . ',';
                                }

                                $packageSerialList = substr($packageSerialList, 0, strrpos($packageSerialList, ","));
                            }
                            if (!empty($pcv['package_batch_info'])) {//批次号

                                foreach ($pcv['goods_batch_info'] as $bk => $bv) {

                                    $packageBatchList .= $bv['batch_no'] . ',';
                                }

                                $packageBatchList = substr($packageBatchList, 0, strrpos($packageBatchList, ","));
                            }

                            $list[] = [
                                'order_no' => $value['order_no'],
                                'nickname' => $value['nickname'],
                                'username' => $value['username'],
                                'customer_name' => $value['customer_name'],
                                'consignee' => $value['consignee'],
                                'phone' => $value['phone'],
                                'address' => $value['address'],
                                'discount' => $value['discount'],
                                'tax_rate' => $value['tax_rate'],
                                'final_money' => $value['final_money'],
                                'real_money' => $value['real_money'],
                                'money' => $value['money'],
                                'remark' => $value['remark'],
                                'out_status' => $out_status_msg,
                                'pay_status' => $pay_status_msg,
                                'refund_date' => $value['refund_date'],
                                'goods_info' => '商品信息>>>',
                                'name' => !empty($exportData[$key]['return_materiel'][$pck]['name']) ? $exportData[$key]['return_materiel'][$pck]['name'] : "",
                                'type_name' => !empty($exportData[$key]['return_materiel'][$pck]['type_name']) ? $exportData[$key]['return_materiel'][$pck]['type_name'] : "",
                                'price' => !empty($exportData[$key]['return_materiel'][$pck]['price']) ? $exportData[$key]['return_materiel'][$pck]['price'] : "",
                                'cost_price' => !empty($exportData[$key]['return_materiel'][$pck]['cost_price']) ? $exportData[$key]['return_materiel'][$pck]['cost_price'] : "",
                                'num' => !empty($exportData[$key]['return_materiel'][$pck]['num']) ? $exportData[$key]['return_materiel'][$pck]['num'] : "",
                                'in_num' => !empty($exportData[$key]['return_materiel'][$pck]['in_num']) ? $exportData[$key]['return_materiel'][$pck]['in_num'] : "",
                                'in_storage_num' => !empty($exportData[$key]['return_materiel'][$pck]['in_storage_num']) ? $exportData[$key]['return_materiel'][$pck]['in_storage_num'] : "",
                                'warehouse' => !empty($exportData[$key]['return_materiel'][$pck]['warehouse']) ? $exportData[$key]['return_materiel'][$pck]['warehouse'] : "",
                                'shelf_name' => !empty($exportData[$key]['return_materiel'][$pck]['shelf_name']) ? $exportData[$key]['return_materiel'][$pck]['shelf_name'] : "",
                                'location_name' => !empty($exportData[$key]['return_materiel'][$pck]['location_name']) ? $exportData[$key]['return_materiel'][$pck]['location_name'] : "",
                                'serial_list' => $goodsSerialList,
                                'batch_list' => $goodsBatchList,
                                'tc_info' => '套餐信息>>>',
                                'package_name' => !empty($pv['package_name']) ? $pv['package_name'] : "",
                                'tc_price' => !empty($pv['price']) ? $pv['price'] : "",
                                'tc_cost_price' => !empty($pv['cost_price']) ? $pv['cost_price'] : "",
                                'tc_num' => !empty($pv['num']) ? $pv['num'] : "",
                                'tc_in_num' => !empty($pv['in_num']) ? $pv['in_num'] : "",
                                'tc_materiel_name' => !empty($pcv['materiel_name']) ? $pcv['materiel_name'] : "",
                                'tc_type_name' => !empty($pcv['type_name']) ? $pcv['type_name'] : "",
                                'tc_goods_num' => !empty($pcv['num']) ? $pcv['num'] : "",
                                'tc_in_storage_num' => !empty($pcv['in_storage_num']) ? $pcv['in_storage_num'] : "",
                                'storage_name' => !empty($pcv['storage_name']) ? $pcv['storage_name'] : "",
                                'storage_address' => !empty($pcv['storage_address']) ? $pcv['storage_address'] : "",
                                'tc_shelf_name' => !empty($pcv['shelf_name']) ? $pcv['shelf_name'] : "",
                                'tc_location_name' => !empty($pcv['location_name']) ? $pcv['location_name'] : "",
                                'tc_serial_list' => !empty($packageSerialList) ? $packageSerialList : "",
                                'tc_batch_list' => !empty($packageBatchList) ? $packageBatchList : ""
                            ];
                        }
                    }
                }
            } elseif (empty($value['return_materiel']) && !empty($value['return_package'])) {//退货套餐都

                foreach ($value['return_package'] as $pk => $pv) {

                    $goodsNum[] = count($pv['package_content']);

                    foreach ($pv['package_content'] as $cv) {
                        $packageSerialList = "";
                        $packageBatchList = "";

                        //退货套餐的序列号和批次号
                        if (!empty($cv['package_serial_info'])) {//序列号

                            foreach ($cv['package_serial_info'] as $sk => $sv) {

                                $packageSerialList .= $sv['serial_number'] . ',';
                            }

                            $packageSerialList = substr($packageSerialList, 0, strrpos($packageSerialList, ","));
                        }
                        if (!empty($cv['package_batch_info'])) {//批次号

                            foreach ($cv['package_batch_info'] as $bk => $bv) {

                                $packageBatchList .= $bv['batch_no'] . ',';
                            }

                            $packageBatchList = substr($packageBatchList, 0, strrpos($packageBatchList, ","));
                        }

                        $list[] = [
                            'order_no' => $value['order_no'],
                            'nickname' => $value['nickname'],
                            'username' => $value['username'],
                            'customer_name' => $value['customer_name'],
                            'consignee' => $value['consignee'],
                            'phone' => $value['phone'],
                            'address' => $value['address'],
                            'discount' => $value['discount'],
                            'tax_rate' => $value['tax_rate'],
                            'final_money' => $value['final_money'],
                            'real_money' => $value['real_money'],
                            'money' => $value['money'],
                            'remark' => $value['remark'],
                            'out_status' => $out_status_msg,
                            'pay_status' => $pay_status_msg,
                            'refund_date' => $value['refund_date'],
                            'goods_info' => '商品信息>>>',
                            'name' => "",
                            'type_name' => "",
                            'price' => "",
                            'cost_price' => "",
                            'num' => "",
                            'in_num' => "",
                            'in_storage_num' => "",
                            'warehouse' => "",
                            'shelf_name' => "",
                            'location_name' => "",
                            'serial_list' => "",
                            'batch_list' => "",
                            'tc_info' => '套餐信息>>>',
                            'package_name' => !empty($pv['package_name']) ? $pv['package_name'] : "",
                            'tc_price' => !empty($pv['price']) ? $pv['price'] : "",
                            'tc_cost_price' => !empty($pv['cost_price']) ? $pv['cost_price'] : "",
                            'tc_num' => !empty($pv['num']) ? $pv['num'] : "",
                            'tc_in_num' => !empty($pv['in_num']) ? $pv['in_num'] : "",
                            'tc_materiel_name' => !empty($cv['materiel_name']) ? $cv['materiel_name'] : "",
                            'tc_type_name' => !empty($cv['type_name']) ? $cv['type_name'] : "",
                            'tc_goods_num' => !empty($cv['num']) ? $cv['num'] : "",
                            'tc_in_storage_num' => !empty($cv['in_storage_num']) ? $cv['in_storage_num'] : "",
                            'storage_name' => !empty($cv['storage_name']) ? $cv['storage_name'] : "",
                            'storage_address' => !empty($cv['storage_address']) ? $cv['storage_address'] : "",
                            'tc_shelf_name' => !empty($cv['shelf_name']) ? $cv['shelf_name'] : "",
                            'tc_location_name' => !empty($cv['location_name']) ? $cv['location_name'] : "",
                            'tc_serial_list' => !empty($packageSerialList) ? $packageSerialList : "",
                            'tc_batch_list' => !empty($packageBatchList) ? $packageBatchList : ""
                        ];
                    }
                }
            }
        }

        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI'];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
        }

        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['order_no'])
                    ->setCellValue('B' . $key, $value['nickname'])
                    ->setCellValue('C' . $key, $value['username'])
                    ->setCellValue('D' . $key, $value['customer_name'])
                    ->setCellValue('E' . $key, $value['consignee'])
                    ->setCellValue('F' . $key, $value['phone'])
                    ->setCellValue('G' . $key, $value['address'])
                    ->setCellValue('H' . $key, $value['discount'])
                    ->setCellValue('I' . $key, $value['tax_rate'])
                    ->setCellValue('J' . $key, $value['final_money'])
                    ->setCellValue('K' . $key, $value['real_money'])
                    ->setCellValue('L' . $key, $value['money'])
                    ->setCellValue('M' . $key, $value['remark'])
                    ->setCellValue('N' . $key, $value['out_status'])
                    ->setCellValue('O' . $key, $value['pay_status'])
                    ->setCellValue('P' . $key, $value['refund_date'])
                    //商品信息
                    ->setCellValue('Q' . $key, $value['goods_info'])
                    ->setCellValue('R' . $key, $value['name'])
                    ->setCellValue('S' . $key, $value['type_name'])
                    ->setCellValue('T' . $key, $value['price'])
                    ->setCellValue('U' . $key, $value['cost_price'])
                    ->setCellValue('V' . $key, $value['num'])
                    ->setCellValue('W' . $key, $value['in_num'])
                    ->setCellValue('X' . $key, $value['in_storage_num'])
                    ->setCellValue('Y' . $key, $value['warehouse'])
                    ->setCellValue('Z' . $key, $value['shelf_name'])
                    ->setCellValue('AA' . $key, $value['location_name'])
                    ->setCellValue('AB' . $key, $value['serial_list'])
                    ->setCellValue('AC' . $key, $value['batch_list'])
                    //套餐信息
                    ->setCellValue('AD' . $key, $value['tc_info'])
                    ->setCellValue('AE' . $key, $value['package_name'])
                    ->setCellValue('AF' . $key, $value['tc_price'])
                    ->setCellValue('AG' . $key, $value['tc_cost_price'])
                    ->setCellValue('AH' . $key, $value['tc_num'])
                    ->setCellValue('AI' . $key, $value['tc_in_num'])
                    ->setCellValue('AJ' . $key, $value['tc_materiel_name'])
                    ->setCellValue('AK' . $key, $value['tc_type_name'])
                    ->setCellValue('AL' . $key, $value['tc_goods_num'])
                    ->setCellValue('AM' . $key, $value['tc_in_storage_num'])
                    ->setCellValue('AN' . $key, $value['storage_name'])
                    ->setCellValue('AO' . $key, $value['storage_address'])
                    ->setCellValue('AP' . $key, $value['tc_shelf_name'])
                    ->setCellValue('AQ' . $key, $value['tc_location_name'])
                    ->setCellValue('AR' . $key, $value['tc_serial_list'])
                    ->setCellValue('AS' . $key, $value['tc_batch_list']);
            }
        }
        CommonService::log('退货记录', $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, '退货记录_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-商品采购表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/28 14:04
     * @return array|void
     */
    public function goodsPurchaseExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
        ], [
            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
        ]);
        if (empty($data['token'])) {

            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        //$array = explode(' ', $data['token']);
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];

        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['b.purchase_time', '>=', $startTime];
            $condition[] = ['b.purchase_time', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['b.purchase_time', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['b.purchase_time', '<=', $endTime];
        }

        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();

        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);

        $userInfo = [];
        if ($adminInfo['id'] == $adminInfo['parent_id']) {
            $userInfo['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        } else {
            $userInfo['a.user_id'] = ['EQ' => $adminInfo['id']];
        }

        $exportData = StatisticsService::statementMaterielExport($condition, $data, $userInfo);
        /* echo "<pre>";
        var_dump($exportData);die; */

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('商品采购统计报表');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "E");
        $sheet->setCellValue('A1', '商品名称（规格）')
            ->setCellValue('B1', '采购价格（元）')
            ->setCellValue('C1', '采购数量')
            ->setCellValue('D1', '总金额（元）')
            ->setCellValue('E1', '总数量');
        $materielLine = [];
        $list = [];

        $materielLine[] = count($exportData['materiel_content_info']);

        foreach ($exportData['materiel_content_info'] as $val) {
            $list[] = [
                'materiel_name' => $val['materiel_name'] . '/' . $val['type_name'] . '（' . $val['materiel_no'] . '）',
                'total_unit_price' => $val['total_unit_price'],
                'num' => $val['num'],
                'total_amount' => $exportData['total_amount'],
                'total_price' => $exportData['total_price']
            ];
        }

        $cell = ['D', 'E'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['materiel_name'])
                    ->setCellValue('B' . $key, $value['total_unit_price'])
                    ->setCellValue('C' . $key, $value['num'])
                    ->setCellValue('D' . $key, $value['total_price'])
                    ->setCellValue('E' . $key, $value['total_amount']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('商品采购统计表', $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, '商品采购统计表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-已销售的商品统计
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/29 14:04
     * @return array|void
     */
    public function statisticsGoodsSoldExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' => [
                'required',
                Rule::In([0, 1]),//成本报表类型:0:商品;1:套餐;
            ],
        ], [

            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'type.required' => '请选择报表类型',
            'type.in' => '报表类型错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }
        if ($data['type'] == 1) {

            $title = '已销售的商品统计表（套餐）';
        } else {
            $title = '已销售的商品统计表（商品）';
        }
        $res = SELF::costStatementHandle($title, $data);
        return response()->json($res);
    }

    /**
     * 导出excel-销售报表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/29 14:04
     * @return array|void
     */
    public function statementSellExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
        ], [
            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
        ]);

        $token = $request->header('token');
        //$token = '6588 Eu17I7B8bwbRklhuVerNjZbwKZ9WByZ/v7Y3VT25qTAwX4uVnVmjZDPtfpHmQRdOtfp8NBbMpCx0AkL7ueRaWruS6GbYyyeVl0iPrYliWKx3IJoArszD5L/0j7RdISqhBgUJn8L+n4jtZBAvJIIulLNT06eeYVHcHWhFW1hExKjFMoHmNbFY8JXvlZA1sAyGA2p3oyUyfFGfJ7zYVDMLWXLlkX0rABLoUOrcxjAlFnuetInvHZnPfZkSPSn3dVoL27N2ukfGZb3OIUuZcHIRN2gGQ4FH6rHvn+iErRKZQKK2AWu5tAj8uqRbrMhxkyn5ywkkzEmEj5KDQT2CB94BzA==';
        if (empty($token)) {
            return fail('', 'token验证失败，请重新登录');
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];

        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
            $condition[] = ['sell_date', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '<=', $endTime];
        }
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();
        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);

        if ($tokenInfo['userId'] == $tokenInfo['mainUserId']) {
            $condition['main_id'] = ['EQ' => $tokenInfo['userId']];
        } else {
            $condition['user_id'] = ['EQ' => $tokenInfo['userId']];
        }
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("销售报表");
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "L");
        $sheet->setCellValue('A1', '订单号')
            ->setCellValue('B1', '客户姓名')
            ->setCellValue('C1', '销售金额（元）')
            ->setCellValue('D1', '占总额百分比（%）')
            ->setCellValue('E1', '数量')
            ->setCellValue('F1', '利润（元）')
            ->setCellValue('G1', '欠款（元）')
            ->setCellValue('H1', '销售时间')
            ->setCellValue('I1', '总销售额（元）')
            ->setCellValue('J1', '总成本（元）')
            ->setCellValue('K1', '总欠款（元）')
            ->setCellValue('L1', '总利润（元）');
        $exportData = StatisticsService::statementSell($condition, $data);
        $materielLine = [];
        $list = [];
        if (!empty($exportData['list'])) {
            $materielLine[] = count($exportData['list']);
            foreach ($exportData['list'] as $val) {
                $list[] = [
                    'order_no' => $val['order_no'],
                    'customer_name' => $val['customer_name'],
                    'final_money' => $val['final_money'],
                    'percentage_total' => (number_format($val['final_money'] / $exportData['total_sell_money'] * 100, 2)) . '%',
                    'goods_num' => $val['total_num'],
                    'profit' => $val['total_income'],
                    'total_debt' => $val['total_debt'],
                    'sell_date' => $val['sell_date'],
                    'total_sell_money' => $exportData['total_sell_money'],
                    'total_cost_price' => $exportData['total_sell_cost'],
                    'total_sell_debt' => $exportData['total_sell_debt'],
                    'total_profit' => $exportData['total_sell_income']
                ];
            }
        }
        $cell = ['I', 'J', 'K', 'L'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['order_no'])
                    ->setCellValue('B' . $key, $value['customer_name'])
                    ->setCellValue('C' . $key, $value['final_money'])
                    ->setCellValue('D' . $key, $value['percentage_total'])
                    ->setCellValue('E' . $key, $value['goods_num'])
                    ->setCellValue('F' . $key, $value['profit'])
                    ->setCellValue('G' . $key, $value['total_debt'])
                    ->setCellValue('H' . $key, $value['sell_date'])
                    ->setCellValue('I' . $key, $value['total_sell_money'])
                    ->setCellValue('J' . $key, $value['total_cost_price'])
                    ->setCellValue('K' . $key, $value['total_sell_debt'])
                    ->setCellValue('L' . $key, $value['total_profit']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('销售报表', $data, '导出共' . count($exportData) . '条');

        return SELF::downloadExcel($spreadsheet, '销售报表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-成本报表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 9:30
     * @return array|void
     */
    public function costStatementExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' => [
                'required',
                Rule::In([0, 1]),//成本报表类型:0:商品;1:套餐;
            ],
        ], [

            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'type.required' => '请选择报表类型',
            'type.in' => '报表类型错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }
        /*if($data['type'] ==1){

            $title='成本报表（套餐）';
        }else{
            $title='成本报表（商品）';
        }*/
        $title = '成本报表';
        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];

        //$array = explode(' ', $data['token']);
        $token = $request->header('token');
        if (empty($token)) {
            return fail('', 'token验证失败，请重新登录');
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();

        if (empty($adminInfo)) return fail('', '导出异常');
        if (!empty($adminInfo['parent_id'])) {
            $condition['a.user_id'] = ['EQ' => $tokenInfo['userId']];
        } else {
            $condition['a.main_id'] = ['EQ' => $tokenInfo['userId']];
        }
        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.out_storage_time', '>=', $startTime];
            $condition[] = ['a.out_storage_time', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['a.out_storage_time', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.out_storage_time', '<=', $endTime];
        }

        $exportData = StatisticsService::statisticsGoodsSoldExport($condition, $data);

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle($title);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "E");
        $sheet->setCellValue('A1', '商品名称（编号）')
            ->setCellValue('B1', '数量')
            //->setCellValue('C1', '售价（元）')
            ->setCellValue('C1', '成本（元）')
            //->setCellValue('E1', '利润（元）')
            ->setCellValue('D1', '总数量')
            ->setCellValue('E1', '总成本')/* ->setCellValue('H1', '总售价（元）')
             ->setCellValue('I1', '总利润（元）')*/
        ;
        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData['sell_materiel_info']);
        foreach ($exportData['sell_materiel_info'] as $val) {

            if ($data['type'] == 1) {
                $name = $val['package_name'];
                $cost = $exportData['total_price'];
            } else {
                $name = $val['materiel_name'] . '/' . $val['type_name'] . '（' . $val['materiel_no'] . '）';
                $cost = $exportData['total_cost'];
            }
            $list[] = [
                'materiel_name' => $name,
                'total_num' => $val['total_num'],
                //'total_price' => $val['total_price'],
                'total_cost' => $val['total_cost'],
                //'total_income' => $val['total_price']-$val['total_cost'],
                'total_amount' => $exportData['total_amount'],
                'z_total_cost' => $cost,
                /*'total_sell_price' => $exportData['total_sell_price'],
                'z_total_income' => $exportData['total_income']*/
            ];
        }

        $cell = ['D', 'E'/*,'H','I'*/];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['materiel_name'])
                    ->setCellValue('B' . $key, $value['total_num'])
                    //->setCellValue('C'.$key, $value['total_price'])
                    ->setCellValue('C' . $key, $value['total_cost'])
                    //->setCellValue('E'.$key, $value['total_income'])
                    ->setCellValue('D' . $key, $value['total_amount'])
                    ->setCellValue('E' . $key, $value['z_total_cost'])/* ->setCellValue('H'.$key, $value['total_sell_price'])
                     ->setCellValue('I'.$key, $value['z_total_income'])*/
                ;
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-支出(收入)明细表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 13:48
     * @return array|void
     */
    public function incomeExpenditureExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' => [
                'required',
                Rule::In([0, 1]),
            ],//收支类型：0-收入 1-支出
        ], [
            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'type.required' => '`报表类型`缺失',
            'type.in' => '`报表类型`错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];

        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
            $condition[] = ['add_time', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '<=', $endTime];
        }
        $condition['type'] = ['EQ' => $data['type']];
        $token = $request->header('token');
        //$token = '6588 Eu17I7B8bwbRklhuVerNjZbwKZ9WByZ/v7Y3VT25qTAwX4uVnVmjZDPtfpHmQRdOtfp8NBbMpCx0AkL7ueRaWruS6GbYyyeVl0iPrYliWKx3IJoArszD5L/0j7RdISqhBgUJn8L+n4jtZBAvJIIulLNT06eeYVHcHWhFW1hExKjFMoHmNbFY8JXvlZA1sAyGA2p3oyUyfFGfJ7zYVDMLWXLlkX0rABLoUOrcxjAlFnuetInvHZnPfZkSPSn3dVoL27N2ukfGZb3OIUuZcHIRN2gGQ4FH6rHvn+iErRKZQKK2AWu5tAj8uqRbrMhxkyn5ywkkzEmEj5KDQT2CB94BzA==';
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }

        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();
        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);;
        $tallyIdArr = [];
        $tallyIdArr01 = [];
        $tallyIdArr02 = [];
        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }
        $condition = [];
        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
            $condition[] = ['add_time', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '<=', $endTime];
        }
        if (!empty($data['storage_id'])) {
            if (!empty($data['type'])) {
                $materielContentList = MaterielContentModel::select('materiel_id')
                    ->where([
                        'repeal_status' => 0,
                        'storage_id' => $data['storage_id'],
                        'main_id' => $tokenInfo['mainUserId']
                    ])->distinct()->get()->toArray();

                if (!empty($materielContentList)) {
                    $materIdArr = array_column($materielContentList, 'materiel_id');
                    $materList = TallyModel::select(
                        'a.id'
                    )->from('tally as a')
                        ->leftJoin('materiel as b', 'a.order_no', '=', 'b.purchase_no')
                        ->where($condition)
                        ->whereIn('b.id', $materIdArr)
                        ->where([
                            'b.delete_flag' => 0,
                            'a.delete_flag' => 0,
                            'b.repeal_status' => 0,
                            ['b.purchase_no', '!=', ''],
                            ['a.order_no', '!=', ''],
                            ['b.pay_status', '!=', 0]
                        ])->get()->toArray();
                    $tallyIdArr01 = array_column($materList, 'id');
                }
                $barGoodsList = BarterGoodsDetailModel::select('barter_id')
                    ->where([
                        'delete_flag' => 0,
                        'repeal_status' => 0,
                        'storage_id' => $data['storage_id'],
                        'main_id' => $tokenInfo['mainUserId']
                    ])->distinct()->get()->toArray();
                if (!empty($barGoodsList)) {
                    $barIdArr = array_column($barGoodsList, 'barter_id');
                    $barList = TallyModel::select(
                        'a.id',
                        'b.id as barter_id',
                        'tally_class_name',
                        'b.order_no'
                    )->from('tally as a')
                        ->leftJoin('barter as b', 'a.order_no', '=', 'b.order_no')
                        ->where($condition)
                        ->whereIn('b.id', $barIdArr)
                        ->where([
                            'b.delete_flag' => 0,
                            'a.delete_flag' => 0,
                            'b.repeal_status' => 0,
                            ['b.order_no', '!=', ''],
                            ['a.order_no', '!=', ''],
                            ['b.pay_status', '!=', 0]
                        ])->get()->toArray();
                    $tallyIdArr02 = array_column($barList, 'id');
                }
            } else {
                $sellGoodsList = SellGoodsDetail::select('sell_id')
                    ->where([
                        'repeal_status' => 0,
                        'warehouse_id' => $data['storage_id'],
                        'main_id' => $tokenInfo['mainUserId']
                    ])->distinct()->get()->toArray();
                if (!empty($sellGoodsList)) {
                    $sellIdArr = array_column($sellGoodsList, 'sell_id');
                    $sellList = TallyModel::select(
                        'a.id'
                    )->from('tally as a')
                        ->leftJoin('sell as b', 'a.order_no', '=', 'b.order_no')
                        ->where($condition)
                        ->whereIn('b.id', $sellIdArr)
                        ->where([
                            'b.delete_flag' => 0,
                            'a.delete_flag' => 0,
                            'b.repeal_status' => 0,
                            ['b.order_no', '!=', ''],
                            ['a.order_no', '!=', ''],
                            ['b.pay_status', '!=', 0]
                        ])->get()->toArray();
                    $tallyIdArr01 = array_column($sellList, 'id');
                }
                $barGoodsList = BarterGoodsDetailModel::select('barter_id')
                    ->where([
                        'delete_flag' => 0,
                        'repeal_status' => 0,
                        'storage_id' => $data['storage_id'],
                        'main_id' => $tokenInfo['mainUserId']
                    ])->distinct()->get()->toArray();
                if (!empty($barGoodsList)) {
                    $barIdArr = array_column($barGoodsList, 'barter_id');
                    $barList = TallyModel::select(
                        'a.id'
                    )->from('tally as a')
                        ->leftJoin('barter as b', 'a.order_no', '=', 'b.order_no')
                        ->where($condition)
                        ->whereIn('b.id', $barIdArr)
                        ->where([
                            'b.delete_flag' => 0,
                            'a.delete_flag' => 0,
                            'b.repeal_status' => 0,
                            ['b.order_no', '!=', ''],
                            ['a.order_no', '!=', ''],
                            ['b.pay_status', '!=', 0]
                        ])->get()->toArray();
                    $tallyIdArr02 = array_column($barList, 'id');
                }
            }
            $tallyIdArr = array_unique(array_merge($tallyIdArr01, $tallyIdArr02));
        }
        if ($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $condition['main_id'] = ['EQ' => $tokenInfo['mainUserId']];
        } else {
            $condition['user_id'] = ['EQ' => $tokenInfo['userId']];
        }

        $condition['delete_flag'] = ['EQ' => 0];
        $totalInfo = TallyModel::selectRaw(
            'SUM(money) as money'
        )->where($condition);

        $incomeList = TallyModel::selectRaw(
            'add_time,money,tally_class_name'
        )->where($condition);

        $tallyClassId = [];
        if (isset($data['tally_class_id'])) {
            $tallyClassInfo = TallyClassModel::where('id', $data['tally_class_id'])->first();

            if ($tallyClassInfo['parent_id'] == 0) {
                //获取二级几张类型信息
                $getTallyClass = TallyClassModel::where('parent_id', $data['tally_class_id'])->get()->toArray();

                if (!empty($getTallyClass)) {
                    foreach ($getTallyClass as $value) {
                        $tallyClassId[] = $value['id'];
                    }
                }
            } else {
                $tallyClassId[] = $data['tally_class_id'];
            }

            $totalInfo->whereIn('tally_class_id', $tallyClassId);

            $incomeList->whereIn('tally_class_id', $tallyClassId);
        }
        if (!empty($tallyIdArr)) {
            $incomeList->whereIn('id', $tallyIdArr);
        }
        $totalInfo = $totalInfo->groupBy('main_id')->first();

        $exportData['total_money'] = empty($totalInfo) ? 0 : $totalInfo->money;

        $exportData['income_list'] = $incomeList->orderBy('money', 'desc')
            ->get()->toArray();
        if ($data['type'] == 1) {
            $title = "支出";
        } else {
            $title = "收入";
        }
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle($title . '明细表');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "E");
        $sheet->setCellValue('A1', $title . '类型')
            ->setCellValue('B1', '金额（元）')
            ->setCellValue('C1', '占总额百分比')
            ->setCellValue('D1', '日期')
            ->setCellValue('E1', '总' . $title . '（元）');
        $materielLine = [];
        $list = [];

        $materielLine[] = count($exportData['income_list']);

        foreach ($exportData['income_list'] as $val) {

            $list[] = [
                'tally_class_name' => $val['tally_class_name'],
                'money' => $val['money'],
                'percentage_total' => (number_format($val['money'] / $exportData['total_money'] * 100, 2)) . '%',
                'add_time' => $val['add_time'],
                'total_money' => $exportData['total_money']

            ];
        }

        $cell = ['E'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['tally_class_name'])
                    ->setCellValue('B' . $key, $value['money'])
                    ->setCellValue('C' . $key, $value['percentage_total'])
                    ->setCellValue('D' . $key, $value['add_time'])
                    ->setCellValue('E' . $key, $value['total_money']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('支出(收入)明细表', $data, '导出共' . count($exportData['income_list']) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '明细表_' . date('Y-m-d'), 'Xlsx');

    }

    /**
     * 导出excel-库存统计表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 15:05
     * @return array|void
     */
    public function inventoryStatisticsExport(Request $request)
    {
        $data = $request->all();
        //$array = explode(' ', $data['token']);
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();

        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);;

        if ($adminInfo['parent_id'] == 0) {

            $data['a.main_id'] = ['EQ' => $adminInfo['id']];
        } else {
            $data['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }

        $exportData = StatisticsService::inventoryStatisticsExport($request->storage_id, $data);
        /*  echo "<pre>";
        var_dump($exportData);die;    */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("库存统计表");
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "C");
        $sheet->setCellValue('A1', '商品名称（规格）')
            ->setCellValue('B1', '总件数')
            ->setCellValue('C1', '总价值');
        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData['detail_info']);
        foreach ($exportData['detail_info'] as $val) {
            $list[] = [
                'materiel_name' => $val['materiel_name'] . '/' . $val['type_name'] . '（' . $val['materiel_no'] . '）',
                'total_amount' => $val['total_amount'],
                'total_cost' => $val['total_cost']
            ];
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['materiel_name'])
                    ->setCellValue('B' . $key, $value['total_amount'])
                    ->setCellValue('C' . $key, $value['total_cost']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('库存统计表', $data, '导出共' . count($exportData) . '条');

        return SELF::downloadExcel($spreadsheet, '库存统计表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-仓库统计表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/2 10:05
     * @return array|void
     */
    public function statementStorageExport(Request $request)
    {
        $data = $request->all();
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);

        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();

        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);;

        if ($adminInfo['parent_id'] == 0) {

            $condition['a.main_id'] = ['EQ' => $adminInfo['id']];
        } else {
            $condition['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }

        $exportData = StatisticsService::statementStorageExport($condition);
        /* echo "<pre>";
         var_dump($exportData);die;    */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("仓库统计表");
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "E");
        $sheet->setCellValue('A1', '仓库名称')
            ->setCellValue('B1', '仓库地址')
            ->setCellValue('C1', '总数量')
            ->setCellValue('D1', '总价值')
            ->setCellValue('E1', '负责人');
        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData['storage_info']);

        foreach ($exportData['storage_info'] as $val) {

            $list[] = [
                'storage_name' => $val['storage_name'],
                'storage_address' => $val['storage_address'],
                'total_amount' => !empty($val['storage_detail_info']['total_amount']) ? $val['storage_detail_info']['total_amount'] : "",
                'total_cost' => !empty($val['storage_detail_info']['total_cost']) ? $val['storage_detail_info']['total_cost'] : "",
                'username' => $val['username']
            ];
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['storage_name'])
                    ->setCellValue('B' . $key, $value['storage_address'])
                    ->setCellValue('C' . $key, $value['total_amount'])
                    ->setCellValue('D' . $key, $value['total_cost'])
                    ->setCellValue('E' . $key, $value['username']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('仓库统计表', $data, '导出共' . count($exportData) . '条');

        return SELF::downloadExcel($spreadsheet, '仓库统计表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 客户报表
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/3 15:28
     * @return JsonResponse
     *
     */
    public function statementCustomerExport(Request $request)
    {

        $data = $request->all();
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        /*  $tokenInfo['userId'] = 9604;
        $tokenInfo['mainUserId'] = 9604;*/
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();
        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);;

        if ($adminInfo['parent_id'] == 0 || $adminInfo['parent_id'] == $adminInfo['id']) {

            $condition['main_id'] = ['EQ' => $adminInfo['id']];
        } else {
            $condition['user_id'] = ['EQ' => $adminInfo['id']];
        }
        $exportData = StatisticsService::statementCustomer($data, $condition);
        /* echo "<pre>";
        var_dump($exportData);die; */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        if (!empty($data['type'])) {
            $title = '客户采购统计表';
        } else {
            $title = '客户销售统计表';
        }
        $sheet->setTitle($title);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "F");
        $sheet->setCellValue('A1', '公司名称')
            ->setCellValue('B1', '客户名称')
            ->setCellValue('C1', '利润（元）')
            ->setCellValue('D1', '欠款（元）');
        if (!empty($data['type'])) {
            $sheet->setCellValue('E1', '销售（元）');
        } else {
            $sheet->setCellValue('E1', '采购（元）');
        }
        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData);
        foreach ($exportData as $val) {
            $list[] = [
                'compony_name' => $val['compony_name'],
                'customer_name' => $val['customer_name'],
                'income_money' => $val['income_money'],
                'customer_debt' => $val['customer_debt'],
                'materiel_money' => $val['materiel_money'],
                'sell_money' => $val['sell_money']
            ];
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['compony_name'])
                    ->setCellValue('B' . $key, $value['customer_name'])
                    ->setCellValue('C' . $key, $value['income_money'])
                    ->setCellValue('D' . $key, $value['customer_debt']);
                if (!empty($data['type'])) {
                    $sheet->setCellValue('E' . $key, $value['materiel_money']);
                } else {
                    $sheet->setCellValue('E' . $key, $value['sell_money']);
                }
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData) . '条');

        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 记账单列表导出
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/4 10:46
     * @return JsonResponse
     *
     */
    public function tallyListExport(Request $request)
    {
        $data = $request->all();
        $token = $request->header('token');
        $token = $data['token'] ?? $token;
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();
        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);

        //参数验证
        $validator = Validator::make($data = $request->except('role_id'), [
            'type' => [
                'required',
                'integer',
                Rule::In([0, 1, 2]),
            ],//收支类型：0-收入 1-支出 2-全部
        ], [
            'type.required' => '参数`type`缺失',
            'type.integer' => '参数`type`仅能为数字',
            'type.in' => '参数`type`不合法',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }

        if ($adminInfo['parent_id'] == 0) {
            $condition[] = ['main_id', '=', $adminInfo['id']];
        } else {
            $condition[] = ['main_id', '=', $adminInfo['parent_id']];
        }
        if ($data['type'] != 2) {
            $condition[] = ['type', '=', $data['type']];
        }
        //查询
        $exportData = TallyModel::where($condition)->where(['delete_flag' => 0])->orderBy('id', 'desc')->get();
        if (!empty($exportData)) $exportData = $exportData->toArray();
        switch ($data['type']) {
            case 2:
                $title = "综合";
                break;
            case 1:
                $title = "支出";
                break;
            case 0:
                $title = "收入";
                break;
        }
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("记账单列表（" . $title . "）");
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "D");
        $sheet->setCellValue('A1', '记账类型')
            ->setCellValue('B1', '记账金额')
            ->setCellValue('C1', '编号')
            ->setCellValue('D1', '记账日期');
        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData);
        if (!empty($exportData)) {
            foreach ($exportData as $val) {
                $type = '';
                if ($val['type'] == 1) {
                    $type = '-';
                }
                $list[] = [
                    'tally_class_name' => $val['tally_class_name'],
                    'money' => $type . $val['money'],
                    'order_no' => $val['order_no'],
                    'add_time' => $val['add_time']
                ];
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['tally_class_name'])
                    ->setCellValue('B' . $key, $value['money'])
                    ->setCellValue('C' . $key, $value['order_no'])
                    ->setCellValue('D' . $key, $value['add_time']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('记账单列表（' . $title . '）', $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, '记账单列表（' . $title . '）_' . date('Y-m-d'), 'Xlsx');

    }

    /**
     * 报溢报损记录导出
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/5 10:46
     * @return JsonResponse
     *
     */
    public function overflowBreakageExport(Request $request)
    {

        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'type' => [//类型：0-报溢 1-报损
                'required',
                Rule::In([0, 1])
            ],
            'repeal_status' => [//撤销状态：0-未撤销 1-已撤销 2-全部
                'required',
                Rule::In([0, 1, 2])
            ],
            'status' => [//	审批状态(即入库状态):0-待审批 1-通过 2-拒绝 3-全部
                'required',
                Rule::In([0, 1, 2, 3])
            ],
        ], [
            'type.required' => '参数`类型`缺失',
            'type.In' => '参数`类型`不合法',
            'repeal_status.required' => '参数`撤销状态`缺失',
            'repeal_status.In' => '参数`撤销状态`不合法',
            'status.required' => '参数`审批状态`缺失',
            'status.In' => '参数`审批状态`不合法',
        ]);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }

        if (isset($request->start_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'start_time' => 'date_format:Y-m-d H:i:s',//页面
            ], [
                'start_time.date_format' => '开始时间格式错误，例：' . date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json(['code' => 1, 'msg' => $infos, 'data' => '']);
            }
        }

        if (isset($request->end_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'end_time' => 'date_format:Y-m-d H:i:s',//页面
            ], [
                'end_time.date_format' => '结束时间格式错误，例：' . date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json(['code' => 1, 'msg' => $infos, 'data' => '']);
            }
        }

        $data = $request->all();
        $tokenInfo = self::checkToken($data['token']);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->where('delete_flag', 0)->first();
        if (empty($adminInfo)) return response()->json(['code' => 1, 'msg' => '导出异常', 'data' => '']);

        if ($adminInfo['parent_id'] == 0) {

            $data['user_id'] = $adminInfo['id'];
            $data['main_id'] = $adminInfo['id'];
        } else {

            $data['user_id'] = $adminInfo['id'];
            $data['main_id'] = $adminInfo['parent_id'];
        }

        $aaInfo = AdminAddendumModel::select('is_share_breakage', 'is_share_overflow')
            ->where(['main_id' => $data['main_id']])->first();

        if ($data['user_id'] == $data['main_id']) {
            $userId['a.main_id'] = ['EQ' => $data['main_id']];
        } else {
            //type类型：0-报溢 1-报损
            if ($data['type']) {
                if ($aaInfo['is_share_breakage']) {
                    $userId['a.main_id'] = ['EQ' => $data['main_id']];
                } else {
                    $userId['a.user_id'] = ['EQ' => $data['user_id']];
                }
            } else {
                if ($aaInfo['is_share_overflow']) {
                    $userId['a.main_id'] = ['EQ' => $data['main_id']];
                } else {
                    $userId['a.user_id'] = ['EQ' => $data['user_id']];
                }
            }
        }

        $exportData = OverflowBreakageService::list($data, $userId);
        /*  $json = '{"code":0,"msg":"success","data":{"current_page":1,"data":[{"id":374,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-07 14:46:19","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":380,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"2.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235915710\",\"batch_id\":54420,\"storage_manage_id\":42659,\"num\":\"2\"},{\"batch_no\":\"smyc20221107142359188888\",\"batch_id\":54420,\"storage_manage_id\":42659,\"num\":\"2\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"\u65b9\u5f0f\u5bf9\u65b9\u8fc7\u540e\u7684\u5feb\u901f\u653e\u6b3e\u540e","main_id":10317,"o_b_main_id":374,"materiel_no":"ELmE943R","created_at":"2022-11-07 14:46:19","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[{"id":27,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":374,"remark":"","created_at":"2022-11-07 14:46:19","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":42,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":27,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235960470\",\"batch_id\":53773,\"storage_manage_id\":42657,\"num\":\"1\"}]","o_b_id":374,"main_id":10317,"created_at":"2022-11-07 14:46:19","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":43,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":27,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235930920\",\"batch_id\":53788,\"storage_manage_id\":42651,\"num\":\"1\"}]","o_b_id":374,"main_id":10317,"created_at":"2022-11-07 14:46:19","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":349,"o_b_main_id":374,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 14:46:19","updated_at":null}]},{"id":369,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-07 13:51:57","updated_at":"2022-11-07 13:52:18","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":25,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":369,"remark":"","created_at":"2022-11-07 13:51:57","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":40,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":25,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":369,"main_id":10317,"created_at":"2022-11-07 13:51:57","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":41,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":25,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":369,"main_id":10317,"created_at":"2022-11-07 13:51:57","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":345,"o_b_main_id":369,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:51:57","updated_at":"2022-11-07 13:52:18"}]},{"id":368,"main_id":10317,"user_id":10317,"status":1,"proof_image":"\/uploads\/2022\/11\/07\/mqEzE0wjHRAldkYq3KQXS8uQyUPltTAoN3whO8GZ.jpg","type":1,"repeal_status":0,"created_at":"2022-11-07 13:50:21","updated_at":"2022-11-07 13:51:05","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":24,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":368,"remark":"\u624b\u673a\u5361\u8def\u4e0e\u6d6e\u4e91\u5934\u8bfa\u5973","created_at":"2022-11-07 13:50:21","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":38,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":24,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":368,"main_id":10317,"created_at":"2022-11-07 13:50:21","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":39,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":24,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":368,"main_id":10317,"created_at":"2022-11-07 13:50:21","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":344,"o_b_main_id":368,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:50:21","updated_at":"2022-11-07 13:51:05"}]},{"id":367,"main_id":10317,"user_id":10317,"status":2,"proof_image":"\/uploads\/2022\/11\/07\/m4fooTpKnSUmwTLaOYVLJQb56uJKJWBuTiYU5wig.jpg","type":1,"repeal_status":0,"created_at":"2022-11-07 13:49:38","updated_at":"2022-11-07 13:49:49","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":23,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":367,"remark":"\u63d0\u524d\u4e86\u8fa3\u75bc\u61c2\u4e48\u7eaa\u59d4","created_at":"2022-11-07 13:49:38","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":36,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":23,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":367,"main_id":10317,"created_at":"2022-11-07 13:49:38","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":37,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":23,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":367,"main_id":10317,"created_at":"2022-11-07 13:49:38","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":343,"o_b_main_id":367,"status":2,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:49:38","updated_at":"2022-11-07 13:49:49"}]},{"id":356,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 17:22:00","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":365,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"},{\"batch_no\":\"smyc2022110416490188888\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":356,"materiel_no":"ELmE943R","created_at":"2022-11-04 17:22:00","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":332,"o_b_main_id":356,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 17:22:00","updated_at":null}]},{"id":353,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 16:50:55","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":362,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":353,"materiel_no":"ELmE943R","created_at":"2022-11-04 16:50:55","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":329,"o_b_main_id":353,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 16:50:55","updated_at":null}]},{"id":352,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 16:49:25","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":361,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":352,"materiel_no":"ELmE943R","created_at":"2022-11-04 16:49:25","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":328,"o_b_main_id":352,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 16:49:25","updated_at":null}]},{"id":340,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 20:07:13","updated_at":"2022-10-29 20:07:21","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":349,"materiel_type_id":48336,"name":"\u94c5\u7b14","type_name":"\u957f","num":"1.000000","image":"","warehouse":"\u7761\u732b\u4e91\u4ed3","warehouse_id":5349,"serial_number":"202","batch_info":"","shelf_id":4588,"shelf_location_id":4596,"remark":"","main_id":10317,"o_b_main_id":340,"materiel_no":"oGjnQQp1","created_at":"2022-10-29 20:07:13","updated_at":null,"materiel_name":"\u94c5\u7b14","storage_name":"\u7761\u732b\u4e91\u4ed3","storage_address":"\u868c\u57e0","is_use_serial":1}],"package_info":[],"check_info":[{"id":316,"o_b_main_id":340,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 20:07:13","updated_at":"2022-10-29 20:07:21"}]},{"id":332,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 19:30:09","updated_at":"2022-10-29 19:31:09","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":341,"materiel_type_id":48334,"name":"\u7ea2\u5bcc\u58eb","type_name":"\u5927","num":"1.000000","image":"\/uploads\/2022\/10\/29\/4WKAGsbvGEX1291j1besvEgxVeekQ73DudMAJ0ar.png","warehouse":"\u4e00\u4ed3\u5e93","warehouse_id":5350,"serial_number":"","batch_info":"[]","shelf_id":1,"shelf_location_id":1,"remark":"\u63a8\u9001\u7684\u8d35\u91d1\u5c5e\u7684\u7a7a\u95f4\u5206\u5272","main_id":10317,"o_b_main_id":332,"materiel_no":"3ilE6S02","created_at":"2022-10-29 19:30:09","updated_at":null,"materiel_name":"\u7ea2\u5bcc\u58eb","storage_name":"\u4e00\u4ed3\u5e93","storage_address":"","is_use_serial":0}],"package_info":[],"check_info":[{"id":308,"o_b_main_id":332,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 19:30:09","updated_at":"2022-10-29 19:31:09"}]},{"id":329,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 19:23:02","updated_at":"2022-10-29 19:23:12","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":339,"materiel_type_id":48352,"name":"\u8461\u8404","type_name":"\u8461\u8404","num":"1.000000","image":"","warehouse":"\u4e00\u4ed3\u5e93","warehouse_id":5350,"serial_number":"","batch_info":"[]","shelf_id":1,"shelf_location_id":1,"remark":"","main_id":10317,"o_b_main_id":329,"materiel_no":"c3T5gc4K","created_at":"2022-10-29 19:23:02","updated_at":null,"materiel_name":"\u8461\u8404","storage_name":"\u4e00\u4ed3\u5e93","storage_address":"","is_use_serial":0}],"package_info":[],"check_info":[{"id":306,"o_b_main_id":329,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 19:23:02","updated_at":"2022-10-29 19:23:12"}]}],"first_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=1","from":1,"last_page":2,"last_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","links":[{"url":null,"label":"pagination.previous","active":false},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=1","label":"1","active":true},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","label":"2","active":false},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","label":"pagination.next","active":false}],"next_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","path":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list","per_page":"10","prev_page_url":null,"to":10,"total":17}}';
       $arr  = json_decode($json,true);

       $exportData = $arr['data']['data']; */
        /*  echo "<pre>";
       var_dump($exportData);die; */

        $title = !empty($data['type']) ? "报损" : "报溢";

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle($title . "记录");
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "Z");
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '操作账号')
            ->setCellValue('C1', '审批状态')
            ->setCellValue('D1', '操作状态')
            ->setCellValue('E1', '创建时间')
            ->setCellValue('F1', '商品信息')
            ->setCellValue('G1', '名称（规格）')
            ->setCellValue('H1', '仓库名称')
            ->setCellValue('I1', '仓库地址')
            ->setCellValue('J1', '序列号')
            ->setCellValue('K1', '批次号')
            ->setCellValue('L1', '商品数量')
            ->setCellValue('M1', '商品描述')
            ->setCellValue('N1', '套餐信息')
            ->setCellValue('O1', '套餐名称')
            ->setCellValue('P1', '套餐数量')
            ->setCellValue('Q1', '套餐描述')
            ->setCellValue('R1', '商品名称')
            ->setCellValue('S1', '仓库名称')
            ->setCellValue('T1', '仓库地址')
            ->setCellValue('U1', '序列号')
            ->setCellValue('V1', '批次号')
            ->setCellValue('W1', '审核信息')
            ->setCellValue('X1', '审核人')
            ->setCellValue('Y1', '手机号')
            ->setCellValue('Z1', '审核备注');

        foreach ($exportData as $key => $value) {

            switch ($value['status']) {
                case 2:
                    $status = "拒绝";
                    break;
                case 1:
                    $status = "通过";
                    break;
                case 0:
                    $status = "待审批";
                    break;
            }

            if ($value['repeal_status']) {
                $repeal_status = "已撤销";
            } else {
                $repeal_status = "未撤销";
            }
            if (!empty($value['goods_info']) && empty($value['package_info'])) {//只有商品没有套餐商品
                $totalNum[] = count($value['goods_info']);
                foreach ($value['goods_info'] as $gk => $gv) {
                    $list[] = [
                        'nickname' => $value['nickname'],
                        'username' => $value['username'],
                        'status' => $status,
                        'repeal_status' => $repeal_status,
                        'created_at' => $value['created_at'],
                        'goods_info' => "商品信息>>>",
                        'goods_name' => $gv['name'] . '/' . $gv['type_name'] . '（' . $gv['materiel_no'] . '）',
                        'warehouse' => $gv['warehouse'],
                        'storage_address' => $gv['storage_address'],
                        'serial_number' => $gv['serial_number'],
                        'batch_info' => $gv['batch_info'],
                        'num' => $gv['num'],
                        'remark' => $gv['remark'],
                        'tc_info' => "套餐信息>>>",
                        'package_name' => "",
                        'tc_num' => "",
                        'tc_remark' => "",
                        'tc_materiel_name' => "",
                        'tc_storage_name' => "",
                        'tc_storage_address' => "",
                        'tc_serial_number' => "",
                        'tc_batch_info' => "",
                        'check_info' => "审核信息>>>",
                        'name' => !empty($value['check_info'][$gk]['name']) ? $value['check_info'][$gk]['name'] : "",
                        'mobile' => !empty($value['check_info'][$gk]['mobile']) ? $value['check_info'][$gk]['mobile'] : "",
                        'refund_remark' => !empty($value['check_info'][$gk]['mobile']) ? $value['check_info'][$gk]['refund_remark'] : ""
                    ];
                }
            } else {

                foreach ($value['package_info'] as $pk => $pv) {
                    if (count($pv['package_content']) >= count($value['goods_info'])) {//套餐商品数量大于等于商品数量
                        $totalNum[] = count($pv['package_content']);
                        foreach ($pv['package_content'] as $ck => $cv) {
                            if (!empty($value['goods_info'][$ck])) {
                                $goodsName = $value['goods_info'][$ck]['name'] . '/' . $value['goods_info'][$ck]['type_name'] . '（' . $value['goods_info'][$ck]['materiel_no'] . '）';
                            }
                            $tcGoodsName = $cv['materiel_name'] . '/' . $cv['type_name'] . '（' . $cv['materiel_no'] . '）';
                            $list[] = [
                                'nickname' => $value['nickname'],
                                'username' => $value['username'],
                                'status' => $status,
                                'repeal_status' => $repeal_status,
                                'created_at' => $value['created_at'],
                                'goods_info' => "商品信息>>>",
                                'goods_name' => !empty($goodsName) ? $goodsName : "",
                                'warehouse' => !empty($value['goods_info'][$ck]['warehouse']) ? $value['goods_info'][$ck]['warehouse'] : "",
                                'storage_address' => !empty($value['goods_info'][$ck]['storage_address']) ? $value['goods_info'][$ck]['storage_address'] : "",
                                'serial_number' => !empty($value['goods_info'][$ck]['serial_number']) ? $value['goods_info'][$ck]['serial_number'] : "",
                                'batch_info' => !empty($value['goods_info'][$ck]['batch_info']) ? $value['goods_info'][$ck]['batch_info'] : "",
                                'num' => !empty($value['goods_info'][$ck]['num']) ? $value['goods_info'][$ck]['num'] : "",
                                'remark' => !empty($value['goods_info'][$ck]['remark']) ? $value['goods_info'][$ck]['remark'] : "",
                                'tc_info' => "套餐信息>>>",
                                'package_name' => $pv['package_name'],
                                'tc_num' => $pv['num'],
                                'tc_remark' => $pv['remark'],
                                'tc_materiel_name' => $tcGoodsName,
                                'tc_storage_name' => $cv['storage_name'],
                                'tc_storage_address' => $cv['storage_address'],
                                'tc_serial_number' => $cv['serial_number'],
                                'tc_batch_info' => $cv['batch_info'],
                                'check_info' => "审核信息>>>",
                                'name' => !empty($value['check_info'][$ck]['name']) ? $value['check_info'][$ck]['name'] : "",
                                'mobile' => !empty($value['check_info'][$ck]['mobile']) ? $value['check_info'][$ck]['mobile'] : "",
                                'refund_remark' => !empty($value['check_info'][$ck]['mobile']) ? $value['check_info'][$ck]['refund_remark'] : "",
                            ];
                        }
                    } else {
                        $totalNum[] = count($value['goods_info']);
                        foreach ($pv['goods_info'] as $gk => $gv) {//商品数量大于套餐商品数量
                            $goodsName = $gv['name'] . '/' . $gv['type_name'] . '（' . $gv['materiel_no'] . '）';
                            if (!empty($pv['package_content'][$gk])) {
                                $tcGoodsName = $pv['package_content'][$gk]['materiel_name'] . '/' . $pv['package_content'][$gk]['type_name'] . '（' . $pv['package_content'][$gk]['materiel_no'] . '）';
                            }
                            $list[] = [
                                'nickname' => $value['nickname'],
                                'username' => $value['username'],
                                'status' => $status,
                                'repeal_status' => $repeal_status,
                                'created_at' => $value['created_at'],
                                'goods_info' => "商品信息>>>",
                                'goods_name' => $goodsName,
                                'warehouse' => $gv['warehouse'],
                                'storage_address' => $gv['storage_address'],
                                'serial_number' => $gv['serial_number'],
                                'batch_info' => $gv['batch_info'],
                                'num' => $gv['num'],
                                'remark' => $gv['remark'],
                                'tc_info' => "套餐信息>>>",
                                'package_name' => !empty($pv['package_name']) ? $pv['package_name'] : "",
                                'tc_num' => !empty($pv['num']) ? $pv['num'] : "",
                                'tc_remark' => !empty($pv['remark']) ? $pv['remark'] : "",
                                'tc_materiel_name' => !empty($tcGoodsName) ? $tcGoodsName : "",
                                'tc_storage_name' => !empty($pv['package_content'][$gk]['storage_name']) ? $pv['package_content'][$gk]['storage_name'] : "",
                                'tc_storage_address' => !empty($pv['package_content'][$gk]['storage_address']) ? $pv['package_content'][$gk]['storage_address'] : "",
                                'tc_serial_number' => !empty($pv['package_content'][$gk]['serial_number']) ? $pv['package_content'][$gk]['serial_number'] : "",
                                'tc_batch_info' => !empty($pv['package_content'][$gk]['batch_info']) ? $pv['package_content'][$gk]['batch_info'] : "",
                                'check_info' => "审核信息>>>",
                                'name' => !empty($value['check_info'][$gk]['name']) ? $value['check_info'][$gk]['name'] : "",
                                'mobile' => !empty($value['check_info'][$gk]['mobile']) ? $value['check_info'][$gk]['mobile'] : "",
                                'refund_remark' => !empty($value['check_info'][$gk]['mobile']) ? $value['check_info'][$gk]['refund_remark'] : "",
                            ];
                        }
                    }
                }
            }
        }

        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'N', 'O', 'P', 'Q', 'W'];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($totalNum as $key => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }

        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $batch_no = "";
                $tc_batch_no = "";
                $value['batch_info'] = json_decode($value['batch_info'], true);
                $value['tc_batch_info'] = json_decode($value['tc_batch_info'], true);
                if (!empty($value['batch_info'])) {
                    foreach ($value['batch_info'] as $bk => $bv) {
                        $batch_no .= $bv['batch_no'] . ',';
                    }
                    $batch_no = substr($batch_no, 0, strrpos($batch_no, ","));
                }

                if (!empty($value['tc_batch_info'])) {
                    foreach ($value['tc_batch_info'] as $tk => $tv) {
                        $tc_batch_no .= $tv['batch_no'] . ',';
                    }

                    $tc_batch_no = substr($tc_batch_no, 0, strrpos($tc_batch_no, ","));
                }
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['nickname'])
                    ->setCellValue('B' . $key, $value['username'])
                    ->setCellValue('C' . $key, $value['status'])
                    ->setCellValue('D' . $key, $value['repeal_status'])
                    ->setCellValue('E' . $key, $value['created_at'])
                    //商品信息
                    ->setCellValue('F' . $key, $value['goods_info'])
                    ->setCellValue('G' . $key, $value['goods_name'])
                    ->setCellValue('H' . $key, $value['warehouse'])
                    ->setCellValue('I' . $key, $value['storage_address'])
                    ->setCellValue('J' . $key, $value['serial_number'])
                    ->setCellValue('K' . $key, $batch_no)
                    ->setCellValue('L' . $key, $value['num'])
                    ->setCellValue('M' . $key, $value['remark'])
                    //套餐商品信息
                    ->setCellValue('N' . $key, $value['tc_info'])
                    ->setCellValue('O' . $key, $value['package_name'])
                    ->setCellValue('P' . $key, $value['tc_num'])
                    ->setCellValue('Q' . $key, $value['tc_remark'])
                    ->setCellValue('R' . $key, $value['tc_materiel_name'])
                    ->setCellValue('S' . $key, $value['tc_storage_name'])
                    ->setCellValue('T' . $key, $value['tc_storage_address'])
                    ->setCellValue('U' . $key, $value['tc_serial_number'])
                    ->setCellValue('V' . $key, $tc_batch_no)
                    //审核人信息
                    ->setCellValue('W' . $key, $value['check_info'])
                    ->setCellValue('X' . $key, $value['name'])
                    ->setCellValue('Y' . $key, $value['mobile'])
                    ->setCellValue('Z' . $key, $value['refund_remark']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title . '记录表', $data, '导出共' . count($exportData) . '条');
        return self::downloadExcel($spreadsheet, $title . '记录表_' . date('Y-m-d'), 'Xlsx');
    }

    //实现对excel的下载
    private static function downloadExcel($spreadsheet, $filename, $format, $imgArr = [])
    {
        if ($format == 'Xlsx') {
            $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        } else if ($format == 'Xls') {
            $contentType = 'application/vnd.ms-excel';
        }

        $filenameUrl = $filename . '.' . strtolower($format);
        $objWriter = IOFactory::createWriter($spreadsheet, $format);
        //保存到服务器临时文件下
        $objWriter->save('./tmp.xlsx');
        //删除临时文件
        unlink('./tmp.xlsx');

        header('Content-Type: ' . $contentType . '; charset=utf-8');    //输出文件类型
        header('Content-Disposition: attachment;filename="' . $filenameUrl . '"');    //输出文件名
        header('Cache-Control: max-age=0');    //强制每次请求直接发送给源服务器，而不经过本地缓存版本的校验
        header('Access-Control-Allow-Origin:*');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        if (!empty($imgArr)) {
            foreach ($imgArr as $iVal) {
                unlink($iVal);
            }
        }
        exit();
    }

    public function materielExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'search_type' => [
                'required',
                Rule::In([0, 1, 2, 3, 4, 5, 6, 7, 8])
            ],
            'status' => [
                'required',
                Rule::In([0, 1, 2, 3, 4, 5, 6])
            ]
        ], [
            'search_type.required' => '参数`搜索类型`缺失',
            'search_type.in' => '参数`搜索类型`不合法',
            'status.required' => '参数`状态`缺失',
            'status.in' => '参数`状态`不合法',
        ]);

        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => ''
            ]);
        }

        $token = $request->header('token');
        $result = self::checkToken($token);

        $adminInfo = AdminModel::where('id', $result['userId'])->first();
        if ($adminInfo['parent_id'] != 0) {
            $parentId = $adminInfo['parent_id'];
        } else {
            $parentId = $result['userId'];
        }

        $materielData = MaterielsService::materielExport($data, $parentId);

        self::export($materielData);
        $materielData['main_id'] = $parentId;
        $materielData['user_id'] = $result['userId'];
        $logData = '导出共' . count($materielData) . '条';
        CommonService::log('采购单导出', $materielData, $logData);
        return true;
    }

    /**
     * 采购单导出代码
     * @param $materielData
     * 2022/10/21 14:46
     * Author: Sun Wu
     * @return bool
     */
    protected function export($materielData)
    {
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('采购单报表导出');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "T");
        $sheet->setCellValue('A1', '供应商名')
            ->setCellValue('B1', '采购日期')
            ->setCellValue('C1', '优惠方式')
            ->setCellValue('D1', '实付金额')
            ->setCellValue('E1', '商品名')
            ->setCellValue('F1', '商品规格')
            ->setCellValue('G1', '商品图')
            ->setCellValue('H1', '单价')
            ->setCellValue('I1', '采购数量')
            ->setCellValue('J1', '序列号')
            ->setCellValue('K1', '仓库名')
            ->setCellValue('L1', '货架')
            ->setCellValue('M1', '库位')
            ->setCellValue('N1', '发货方式')
            ->setCellValue('O1', '运输方式')
            ->setCellValue('P1', '物流公司')
            ->setCellValue('Q1', '物流单号')
            ->setCellValue('R1', '发货人')
            ->setCellValue('S1', '发货人联系方式')
            ->setCellValue('T1', '备注');
        $materielLine = [];
        $list = [];
        foreach ($materielData as $key => $value) {
            $materielLine[] = count($value['materiel_content']);
            foreach ($value['materiel_content'] as $val) {
                $list[] = [
                    'supplier_name' => $value['supplier_name'],
                    'purchase_time' => $value['purchase_time'],
                    'preferential_way' => $value['preferential_way'],
                    'final_money' => $value['final_money'],
                    'materiel_name' => $val['materiel_name'],
                    'type_name' => $val['type_name'],
                    'materiel_img' => $val['materiel_img'],
                    'unit_price' => $val['unit_price'],
                    'num' => $val['num'],
                    'serial_no' => $val['serial_no'],
                    'storage_name' => $val['storage_name'],
                    'shelf_name' => $val['shelf_name'],
                    'location_name' => $val['location_name'],
                    'shipping_method' => $value['shipping_method'],
                    'transport_type' => $value['transport_type'],
                    'logistics_company' => empty($value['logistics_company']) ? '' : $value['logistics_company'],
                    'logistics_order_no' => empty($value['logistics_order_no']) ? '' : $value['logistics_order_no'],
                    'deliver_person' => empty($value['deliver_person']) ? '' : $value['deliver_person'],
                    'deliver_phone' => empty($value['deliver_phone']) ? '' : $value['deliver_phone'],
                    'remark' => empty($value['remark']) ? '' : $value['remark'],
                ];

            }
        }

        $cell = ['A', 'B', 'C', 'D', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN'];//需要合并的单元格

        foreach ($cell as $item) {
            $start = 2;
            foreach ($materielLine as $k => $value) {
                $end = ($start + $value) - 1;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                $start = $end + 1;
            }
        }

        if (!empty($list)) {
            foreach ($list as $key => $mData) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $mData['supplier_name'])
                    ->setCellValue('B' . $key, $mData['purchase_time']);

                switch ($mData['preferential_way']) {
                    case 0:
                        $sheet->setCellValue('C' . $key, '无');
                        break;
                    case 1:
                        $sheet->setCellValue('C' . $key, '修改价格');
                        break;
                    case 2:
                        $sheet->setCellValue('C' . $key, '折扣优惠');
                        break;
                }

                $sheet->setCellValue('D' . $key, $mData['final_money'])
                    ->setCellValue('E' . $key, $mData['materiel_name'])
                    ->setCellValue('F' . $key, $mData['type_name'])
                    ->setCellValue('H' . $key, $mData['unit_price'])
                    ->setCellValue('I' . $key, $mData['num'])
                    ->setCellValue('J' . $key, $mData['serial_no'])
                    ->setCellValue('K' . $key, $mData['storage_name'])
                    ->setCellValue('L' . $key, $mData['shelf_name'])
                    ->setCellValue('M' . $key, $mData['location_name']);

                switch ($mData['shipping_method']) {
                    case 1:
                        $sheet->setCellValue('N' . $key, '自提');
                        break;
                    case 2:
                        $sheet->setCellValue('N' . $key, '代发');
                        break;
                    case 3:
                        $sheet->setCellValue('N' . $key, '送货');
                        break;
                }

                switch ($mData['transport_type']) {
                    case 1:
                        $sheet->setCellValue('O' . $key, '空运');
                        break;
                    case 2:
                        $sheet->setCellValue('O' . $key, '海运');
                        break;
                    case 3:
                        $sheet->setCellValue('O' . $key, '铁路');
                        break;
                    case 4:
                        $sheet->setCellValue('O' . $key, '公路');
                        break;
                }

                if (!empty($mData['materiel_img'])) {
                    $imgPath = config('app.aliyun_oss_link') . $mData['materiel_img'];
                    $sheet->setCellValue('G' . $key, $imgPath);
                    $client = $this->ossClient;
                    $ossPath = ltrim($mData['materiel_img'], './');
                    $exist = $client->doesObjectExist(config('app.aliyun_oss_bucket'), $ossPath);

                    if (!$exist) {
                        $sheet->setCellValue('G' . $key, '');
                    } else {
                        $fileInfo = pathinfo($imgPath);
                        $sheet->setCellValue('G' . $key, '');
                        //过滤非文件类型
//                        if(!empty($fileInfo['basename'])) {
//                            $drawing[$key] = new Drawing();
//                            $drawing[$key]->setResizeProportional(false);
//                            $drawing[$key]->setName('商品图');
//                            $drawing[$key]->setDescription('商品图');
//                            $drawing[$key]->setPath('');
////                            $drawing[$key]->setPath($imgPath);
//                            $drawing[$key]->setWidth(40);
//                            $drawing[$key]->setHeight(40);
//                            $drawing[$key]->setOffsetX(10);
//                            $drawing[$key]->setOffsetY(10);
//                            $drawing[$key]->setCoordinates('G'.$key);
//                            $drawing[$key]->setWorksheet($sheet);
//                        }
                    }
                } else {
                    $sheet->setCellValue('G' . $key, '');
                }

                $sheet->setCellValue('P' . $key, empty($mData['logistics_company']) ? '' : $mData['logistics_company']);
                $sheet->setCellValue('Q' . $key, empty($mData['logistics_order_no']) ? '' : $mData['logistics_order_no']);
                $sheet->setCellValue('R' . $key, empty($mData['deliver_person']) ? '' : $mData['deliver_person']);
                $sheet->setCellValue('S' . $key, empty($mData['deliver_phone']) ? '' : $mData['deliver_phone']);
                $sheet->setCellValue('T' . $key, empty($mData['remark']) ? '' : $mData['remark']);
            }
        }

        ob_end_clean();

        self::downloadExcel($spreadsheet, '采购单报表_' . date('Y-m-d'), 'Xlsx');
        return true;
    }

    /**
     * 导出excel-库存
     * @param Request $request
     * AUTHOR  : Sun Wu
     * DateTime: 2022/7/30 11:34
     * @return array|void
     */
    public function storageManageExport(Request $request)
    {
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if (!$tokenInfo) {
            return $tokenInfo;
        }

        $user = [];
        if (!$tokenInfo['userType']) {
            $user['a.main_id'] = $tokenInfo['mainUserId'];
        } else {
            $user['a.user_id'] = $tokenInfo['userId'];
        }
        /*$user['a.main_id'] = 19210;
        $tokenInfo['mainUserId']=19210;
        $tokenInfo['userId']=19210;*/

        $data = $request->all();

        $mainInfo = AdminAddendumModel::where('main_id', $tokenInfo['mainUserId'])->first();
        $data['user_id'] = $tokenInfo['userId'];
        $data['main_id'] = $tokenInfo['mainUserId'];
        if ($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $mainId['storage_manage.main_id'] = ['EQ' => $tokenInfo['mainUserId']];
        } else {
            if ($mainInfo['is_share_store']) {
                $mainId['storage_manage.main_id'] = ['EQ' => $tokenInfo['mainUserId']];
            } else {
                $mainId['e.user_id'] = ['EQ' => $tokenInfo['userId']];
            }
        }

        $data['order_type'] = empty($data['order_type']) ? 0 : $data['order_type'];

        $data['order_by'] = empty($data['order_by']) ? 'asc' : $data['order_by'];

        if (!in_array($data['order_by'], ['desc', 'asc'])) {
            return response()->json([
                'code' => 1,
                'msg' => '不支持的排序方式',
                'data' => ''
            ]);
        }

        //获取导出的数据
        $exportData = CommonStorageOperateService::exportData($mainId, $data);

        $logData = '导出共' . count($exportData) . '条';

        @ini_set('memory_limit', '1024M');
        //@ini_set('max_execution_time', 30);
        //set_time_limit(0);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('库存导出报表');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "Q");
        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '编号/货号')
            ->setCellValue('C1', '商品名（第二名称）')
            ->setCellValue('D1', '型号/规格')
            ->setCellValue('E1', '仓库名')
            ->setCellValue('F1', '一级分类')
            ->setCellValue('G1', '二级分类')
            ->setCellValue('H1', '单位')
            ->setCellValue('I1', '条形码')
            ->setCellValue('J1', '销售价')
            ->setCellValue('K1', '最低销售价')
            ->setCellValue('L1', '成本价')
            ->setCellValue('M1', '库存数')
            ->setCellValue('N1', '备注')
            ->setCellValue('O1', '商品/套餐;0:商品:1:套餐;')
            ->setCellValue('P1', '未使用序列号')
            ->setCellValue('Q1', '已使用序列号');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);

        $singleGoodsCostArr = [];
        $totalInStorageNumArr = [];
        $key = 0;
        $imgPathArr = [];
        foreach ($exportData as $key => $value) {
            $key += 2;
            $serialList = "";
            /*  $localpath = "";
            if(!empty($value['materiel_img'])) {
                //$sheet->setCellValue('A'.$key, '');
                $localpath = public_path($value['materiel_img']);
                if(!file_exists($localpath)) {
                    $uploadPath = date('Y').'/'.date('m').'/'.date('d');
                    $path = public_path('/uploads/'.$uploadPath);
                    $filePpath = strstr($value['materiel_img'], 'uploads/');
                    $localpath = $path.'/'.time().rand(1000,9999).$value['id'].'.png';
                    $options = array(
                        $this->ossClient::OSS_FILE_DOWNLOAD => $localpath
                    );
                    if (!file_exists($path)) {
                        @mkdir($path, 0777, true);
                    }
                    try {
                        $imgPath = $this->ossClient->getObject(config('app.aliyun_oss_bucket'), $filePpath,$options);
                        $imgPathArr[] = $localpath;
                    } catch (\Exception $e) {
                    }
                }
                $fileInfo = pathinfo($localpath);
                //过滤非文件类型
                if(!empty($fileInfo['basename'])) {
                    $drawing[$key] = new Drawing();
                    $drawing[$key]->setResizeProportional(false);
                    $drawing[$key]->setName('商品图');
                    $drawing[$key]->setDescription('商品图');
                    $drawing[$key]->setPath($localpath);
                    $drawing[$key]->setWidth(50);
                    $drawing[$key]->setHeight(50);
                    $drawing[$key]->setOffsetX(10);
                    $drawing[$key]->setOffsetY(10);
                    $drawing[$key]->setCoordinates('A'.$key);
                    $drawing[$key]->setWorksheet($sheet);
                }
            }
            else {
                $sheet->setCellValue('A'.$key, '');
            } */
            $sheet->setCellValue('A' . $key, '');
            $serialData = [];
            $usedSerialData = [];

            if ($value['is_use_serial'] == 1) {
                $serialData = SerialNumberModel::select('serial_no')->where([
                    'storage_manage_id' => $value['id'],
                    'goods_id' => $value['type_id'],
                    'status' => 1,
                    'delete_flag' => 0
                ])->pluck('serial_no')->toArray();
                //已使用序列号
                $usedSerialData = SerialNumberModel::select('serial_no')->where([
                    'storage_manage_id' => $value['id'],
                    'goods_id' => $value['type_id'],
                    'status' => 0,
                    'delete_flag' => 0
                ])->pluck('serial_no')->toArray();
            }

            $singleGoodsCostArr[] = $value['materiel_mean_cost'] * $value['in_storage_num'];
            $totalInStorageNumArr[] = $value['in_storage_num'];

            $sheet->setCellValue('B' . $key, $value['materiel_no'])
                ->setCellValue('C' . $key, $value['materiel_name'] . '（' . $value['two_materiel_name'] . '）')
                ->setCellValue('D' . $key, $value['type_name'])
                ->setCellValue('E' . $key, $value['storage_name'])
                ->setCellValue('F' . $key, $value['f_class_name'])
                ->setCellValue('G' . $key, $value['s_class_name'])
                ->setCellValue('H' . $key, $value['materiel_unit'])
                ->setCellValue('I' . $key, $value['bar_code'])
                ->setCellValue('J' . $key, $value['selling_price'])
                ->setCellValue('K' . $key, $value['lower_selling_price'])
                ->setCellValue('L' . $key, $value['materiel_mean_cost'])
                ->setCellValue('M' . $key, $value['in_storage_num'])
                ->setCellValue('N' . $key, $value['remark'])
                ->setCellValue('O' . $key, $value['is_product_materiel'])
                ->setCellValue('P' . $key, implode(',', $serialData))
                ->setCellValue('Q' . $key, implode(',', $usedSerialData));
        }

        $singleGoodsCost = 0;
        foreach ($singleGoodsCostArr as $val) {
            $singleGoodsCost += sprintf('%.6f', $val);
        }

        $totalInStorageNum = 0;
        foreach ($totalInStorageNumArr as $val) {
            $totalInStorageNum += sprintf('%.6f', $val);
        }
        if (!empty($exportData)) {
            $sheet->setCellValue('R1', '总成本:' . $singleGoodsCost)
                ->setCellValue('R2', '总库存:' . $totalInStorageNum);
        }
        $spreadsheet->getActiveSheet()->getStyle('Q2')->getAlignment()->setWrapText(true);

        $exportData['main_id'] = $tokenInfo['mainUserId'];
        $exportData['user_id'] = $tokenInfo['userId'];
        CommonService::log('库存导出报表', $exportData, $logData);
        return SELF::downloadExcel($spreadsheet, '库存报表_' . date('Y-m-d'), 'Xlsx'/* ,$imgPathArr */);

    }

    /**
     * 批量导出excel-盘点记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2023/10/24 11:34
     * @return array|void
     */
    public function batchCheckInventoryExport(Request $request)
    {
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if (!$tokenInfo) {
            return $tokenInfo;
        }
        /*$tokenInfo['mainUserId']=9604;
        $tokenInfo['userId']=9604;*/
        $condition['a.main_id'] = $tokenInfo['mainUserId'];
        $keyword = empty($request->keyword) ? '' : $request->keyword;
        $data = $request->all();
        if (empty($data['check_id'])) {
            return response()->json([
                'code' => 1,
                'msg' => '盘点记录ID，不能为空',
                'data' => ''
            ]);
        }

        $checkData = CheckStorageManageService::batchCheckInventoryExport($condition, $data, $keyword);
        //RETURN response()->json($checkData);
        $logData = '导出共' . count($checkData) . '条';
        if (empty($checkData)) {
            return response()->json([
                'code' => 1,
                'msg' => '暂无可导出库存数据',
                'data' => ''
            ]);
        }
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('盘点记录报表');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "M");
        $sheet->setCellValue('A1', '盘点单号')
            ->setCellValue('B1', '盘点人')
            ->setCellValue('C1', '盘点类型')
            ->setCellValue('D1', '盘点时间')
            ->setCellValue('E1', '商品名称（类型）')
            ->setCellValue('F1', '商品编号')
            ->setCellValue('G1', '仓库/库位/货架')
            ->setCellValue('H1', '单位')
            ->setCellValue('I1', '数量')
            ->setCellValue('J1', '盘点数')
            ->setCellValue('K1', '盘亏盘盈')
            ->setCellValue('L1', '已有的序列号')
            ->setCellValue('M1', '删除的序列号');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $goodsNum = [];
        $list = [];
        if (!empty($checkData)) {
            foreach ($checkData as $val) {
                if (!empty($val['goods_list'])) {//商品信息
                    $goodsNum[] = count($val['goods_list']);
                    if ($val['sg_type'] == 1) {
                        $type = '整仓盘点';

                    } else {
                        $type = '商品盘点';
                    }
                    foreach ($val['goods_list'] as $k => $item) {
                        if ($item['yk_flag'] == 1) {
                            $flag = '盘盈';
                        } elseif ($item['yk_flag'] == 2) {
                            $flag = '盘亏';
                        } else {
                            $flag = '正常';
                        }
                        $list[] = [
                            'check_no' => $val['check_no'] . ' ',//盘点单号
                            'nickname' => !empty($val['nickname']) ? $val['nickname'] : $val['username'],//操作人昵称
                            'sg_type' => $type,//盘点类型
                            'check_time' => $val['check_time'],//日期
                            'materiel_name' => $item['materiel_name'] . '/' . $item['type_name'], //商品名称/类型
                            'materiel_no' => $item['materiel_no'],//商品编号
                            'storage_name' => $item['storage_name'] . '/' . $item['shelf_name'] . '/' . $item['location_name'],//仓库/库位/货架
                            'materiel_unit' => $item['materiel_unit'],//单位
                            'in_storage_num' => $item['in_storage_num'],//数量
                            'yk_num' => $item['yk_num'],//盈亏数
                            'yk_flag' => $flag,//盘盈亏
                            'serial_number_list' => $item['serial_number_list'],//现有序列号
                            'del_serial_number_list' => $item['del_serial_number_list'],//已删除序列号
                        ];
                    }
                }
            }
        }
        $cell = ['A', 'B', 'C', 'D'];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
        }

        $key = 0;
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['check_no'])
                    ->setCellValue('B' . $key, $value['nickname'])
                    ->setCellValue('C' . $key, $value['sg_type'])
                    ->setCellValue('D' . $key, $value['check_time'])
                    ->setCellValue('E' . $key, $value['materiel_name'])
                    ->setCellValue('F' . $key, $value['materiel_no'])
                    ->setCellValue('G' . $key, $value['storage_name'])
                    ->setCellValue('H' . $key, $value['materiel_unit'])
                    ->setCellValue('I' . $key, $value['in_storage_num'])
                    ->setCellValue('J' . $key, $value['yk_num'])
                    ->setCellValue('K' . $key, $value['yk_flag'])
                    ->setCellValue('L' . $key, $value['serial_number_list'])
                    ->setCellValue('M' . $key, $value['del_serial_number_list']);
            }
        }
        $checkData['main_id'] = $tokenInfo['mainUserId'];
        $checkData['user_id'] = $tokenInfo['userId'];
        CommonService::log('导出盘点记录报表', $checkData, $logData);

        return SELF::downloadExcel($spreadsheet, '盘点记录报表_' . date('Y-m-d'), 'Xlsx');
    }

    static private function checkToken($token)
    {
        if (empty($token)) {
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        $array = explode(' ', $token);
        //私钥获取
        $private = SystemConfigModel::getConfigValue('private');

        if (count($array) < 2) {
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        //解密加密参数并验证
        $rsadata = DBRSAUtil::rsaDecrypt($array[1], $private);
        $rsadata = json_decode($rsadata, true);

        if ($rsadata['userId'] != $array['0']) {
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        return $rsadata;
    }

    public function storageStartExport(Request $request)
    {
        $data = $request->all();

        $data['materiel_class_id'] = empty($data['materiel_class_id']) ? 0 : $data['materiel_class_id'];
        //撤销状态;0:未撤销;1:已撤销;
        $data['status'] = empty($data['status']) ? 0 : $data['status'];
        $data['type'] = empty($data['type']) ? 0 : $data['type'];//操作日志类型;0:入库;1:出库;
        $data['log_type'] = empty($data['log_type']) ? 0 : $data['log_type'];
        //$token = '6588 JCnNLIVVeHhz9Mx0fbBpNEeq+EziVjsCMpZVPUz4YwVYND4UQ2mGR7ZB20tOWT9n3A6qPVIlKdH0Av6LWuJiGwInxK9h/pEZaL/BFEKpsskkIH74o5+IgYWYG5r29zLba2QTtC2f87LWPiIH49beYW01Ip5nn8aaM5Qf7ip7HLgHMUwTZ71CeLeqT8zmw4BvnCh6ZQdW8jJ+cb22lebHxl5nFt1ZDm4GkTceLHLXu3tblEZzH62n0YUdd/EvYFMy/QgbC0yYf560f8I2eHbihLTE8Q+QDdwvSD0Vv+KRIJqlmgqUbMgpWhImik0P4DnRbDIHx1M0y3VPAxajL9n/4A==';
        $token = $request->header('token');
        $userInfo = self::checkToken($token);
        $data['user_id'] = $userInfo['userId'];
        $data['label_id'] = empty($data['label_id']) ? '' : $data['label_id'];
        $data['main_id'] = $userInfo['mainUserId'];
        $mainInfo = AdminAddendumModel::where('main_id', $data['main_id'])->first();
        $data['is_share_log'] = $mainInfo['is_share_operating_record'];

        @ini_set('memory_limit', '1024M');
        @set_time_limit(30);
        @ini_set('max_execution_time', 100);
        ob_end_clean();
        ob_start();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('出入库记录报表');
        $keywordCondition = [];
        if (!empty($data['keyword'])) {
            switch ($data['keyword_type']) {
                case 0://商品名
                    $keywordCondition[] = ['e.materiel_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 1://型号名
                    $keywordCondition[] = ['e.type_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 2://商品编号
                    $keywordCondition[] = ['e.materiel_no', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 3://仓库名
                    $keywordCondition[] = ['a.storage_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 4://序列号
                    $keywordCondition[] = ['a.serial_number', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 5://操作人名称
                    $keywordCondition[] = ['d.nickname', 'like', '%' . $data['keyword'] . '%'];
                    break;
            }
        }

        switch ($data['type']) {
            case 0:
                self::inLogExport($data, $spreadsheet, $sheet, $keywordCondition);
                break;
            case 1:
                self::outLogExport($data, $spreadsheet, $sheet, $keywordCondition);
                break;
        }

        ob_end_clean();
        return true;
    }

    public function outInGoodsExport(Request $request)
    {
        $data = $request->all();
        $data['materiel_class_id'] = empty($data['materiel_class_id']) ? 0 : $data['materiel_class_id'];
        //撤销状态;0:未撤销;1:已撤销;
        $data['status'] = empty($data['status']) ? 0 : $data['status'];
        $data['type'] = empty($data['type']) ? 0 : $data['type'];//操作日志类型;0:入库;1:出库;
        $data['log_type'] = empty($data['log_type']) ? 0 : $data['log_type'];
        //$token = '6588 JCnNLIVVeHhz9Mx0fbBpNEeq+EziVjsCMpZVPUz4YwVYND4UQ2mGR7ZB20tOWT9n3A6qPVIlKdH0Av6LWuJiGwInxK9h/pEZaL/BFEKpsskkIH74o5+IgYWYG5r29zLba2QTtC2f87LWPiIH49beYW01Ip5nn8aaM5Qf7ip7HLgHMUwTZ71CeLeqT8zmw4BvnCh6ZQdW8jJ+cb22lebHxl5nFt1ZDm4GkTceLHLXu3tblEZzH62n0YUdd/EvYFMy/QgbC0yYf560f8I2eHbihLTE8Q+QDdwvSD0Vv+KRIJqlmgqUbMgpWhImik0P4DnRbDIHx1M0y3VPAxajL9n/4A==';
        $token = $request->header('token');
        $userInfo = self::checkToken($token);
        $data['user_id'] = $userInfo['userId'];
        $data['label_id'] = empty($data['label_id']) ? '' : $data['label_id'];
        $data['main_id'] = $userInfo['mainUserId'];
        $mainInfo = AdminAddendumModel::query()->where('main_id', $data['main_id'])->first();
        $data['is_share_log'] = $mainInfo['is_share_operating_record'];
        @ini_set('memory_limit', '1024M');
        @set_time_limit(30);
        @ini_set('max_execution_time', 100);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('出入库记录报表');
        $keywordCondition = [];
        if (!empty($data['keyword'])) {
            switch ($data['keyword_type']) {
                case 0://商品名
                    $keywordCondition[] = ['b.materiel_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 1://型号名
                    $keywordCondition[] = ['b.type_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 2://商品编号
                    $keywordCondition[] = ['b.materiel_no', 'like', '%' . $data['keyword'] . '%'];
                    break;
                case 3://仓库名
                    $keywordCondition[] = ['a.storage_name', 'like', '%' . $data['keyword'] . '%'];
                    break;
            }
        }
        self::inGoodsExport($data, $spreadsheet, $sheet, $keywordCondition);
        ob_end_clean();
        return true;
    }

    /**
     * 出入操作记录导入
     */
    public function logImport(Request $request)
    {
        $req = $request->all();
        $token = $request->header('token');
        if (empty($token)) {
            return response()->json(['code' => 2, 'msg' => 'token不能为空', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);

        if (!$tokenInfo) {
            return $tokenInfo;
        }
        $mainId = $tokenInfo['mainUserId'];
        $files = $request->file("log_templete");
        $ext = $files->extension();
        if ($files->getSize() > 10 * 1024 * 1024) {
            return response()->json(['code' => 1, 'msg' => '文件大小不能超出10M', 'data' => '']);
        }

        //application/vnd.ms-excel  为xls或xlsx文件类型
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return response()->json(['code' => 1, 'msg' => '必须为excel表格，且必须为xlsx或xls格式！', 'data' => '']);
        }

        if ($ext == 'xls') {
            $reader = IOFactory::createReader('Xls');
        } else if ($ext == 'xlsx') {
            $reader = IOFactory::createReader('Xlsx');
        }

        $path = $files->store(date('Y') . '/' . date('m') . '/' . date('d'));
        $filePath = public_path() . '/uploads/' . $path;

        $objPHPExcel = $reader->load($filePath);
        $sheet = $objPHPExcel->getActiveSheet();
        SELF::fillEmptyMergeCells($sheet);//处理合并单元格的数据
        $excelSheet = $objPHPExcel->getSheet(0);//读取第一个工作表
        $highestColumn = $excelSheet->getHighestColumn();   // 取得总列数
        $highestRow = $excelSheet->getHighestRow();// 取得总行数

        $data = [];
        $n = $objPHPExcel->getActiveSheet()->getCell('A1')->getValue();

        for ($j = 2; $j <= $highestRow; $j++) {
            $n = $j - 2;
            $data[$n] = [
                'nickname' => $objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue(),
                'operate_time' => $objPHPExcel->getActiveSheet()->getCell('B' . $j)->getValue(),
                'materiel_name' => $objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue(),
                'sell_price' => $objPHPExcel->getActiveSheet()->getCell('D' . $j)->getValue(),
                'serial_number' => $objPHPExcel->getActiveSheet()->getCell('E' . $j)->getValue(),
                'operate_num' => $objPHPExcel->getActiveSheet()->getCell('F' . $j)->getValue(),
                'next_storage_num' => $objPHPExcel->getActiveSheet()->getCell('G' . $j)->getValue(),
                'storage_name' => $objPHPExcel->getActiveSheet()->getCell('H' . $j)->getValue(),
                'type' => $objPHPExcel->getActiveSheet()->getCell('I' . $j)->getValue(),
                'purchase_no' => $objPHPExcel->getActiveSheet()->getCell('J' . $j)->getValue(),
                'last_storage_num' => $objPHPExcel->getActiveSheet()->getCell('K' . $j)->getValue(),
                'next_storage_name' => $objPHPExcel->getActiveSheet()->getCell('L' . $j)->getValue(),
                'remark' => $objPHPExcel->getActiveSheet()->getCell('M' . $j)->getValue(),
                'label_name' => $objPHPExcel->getActiveSheet()->getCell('N' . $j)->getValue(),
                'created_at' => $objPHPExcel->getActiveSheet()->getCell('O' . $j)->getValue(),
            ];
        }
        $time = time();
        if (!empty($data)) {
            $materInfo = [];
            $frontStorInfo = [];
            $nextStorInfo = [];
            $classInfo = [];
            foreach ($data as $key => $val) {
                if (!empty($val['materiel_name'])) {
                    if (strpos($val['materiel_name'], '/') !== false) {
                        $materCond = [];
                        $materInfo = explode('/', $val['materiel_name']);
                        $materCond['materiel_name'] = $materInfo[0];
                        switch (count($materInfo)) {
                            case 2:
                                $materCond['type_name'] = $materInfo[1];
                                break;
                            case 3:
                                $materCond['type_name'] = $materInfo[1];
                                $materCond['materiel_no'] = $materInfo[2];
                                break;
                        }

                    } else {
                        $materCond['materiel_name'] = $val['materiel_name'];
                    }
                    if (!empty($materCond['materiel_name'])) {
                        $materInfo = MaterielTypeModel::where($materCond)
                            ->where([
                                'main_id' => $mainId,
                                'delete_flag' => 0
                            ])->first();
                    }
                    if (empty($materInfo)) {
                        $materInfo = $materCond;
                    }
                    if (!empty($materInfo['materiel_class_id'])) {
                        $classInfo = MaterielClassModel::where([
                            'id' => $materInfo['materiel_class_id'],
                            'main_id' => $mainId,
                            'delete_flag' => 0
                        ])->first();
                    }


                    if (!empty($val['storage_name'])) {//查询操作前仓库信息
                        if (strpos($val['storage_name'], '/') !== false) {
                            $storCond = [];
                            $frontStorInfo = explode('/', $val['storage_name']);
                            if (!empty($frontStorInfo[0])) {
                                $storCond['storage_name'] = $frontStorInfo[0];
                                if (count($frontStorInfo) == 2) {
                                    $storCond['storage_address'] = $frontStorInfo[1];
                                }
                            }
                        } else {
                            $storCond['storage_name'] = $val['storage_name'];
                        }
                        if (!empty($storCond['storage_name'])) {
                            $frontStorInfo = StorageModel::where($storCond)
                                ->where([
                                    'main_id' => $mainId,
                                    'delete_flag' => 0
                                ])->first();
                        }

                        if (empty($frontStorInfo)) {
                            $frontStorInfo = $storCond;
                        }

                        if (!empty($frontStorInfo['id']) && !empty($materInfo['id'])) {
                            $frontStorageManageInfo = StorageManageModel::where([
                                'type_id' => $materInfo['id'],
                                'storage_id' => $frontStorInfo['id'],
                                'main_id' => $mainId,
                                'delete_flag' => 0
                            ])->first();
                        }
                    }

                    if (!empty($val['next_storage_name'])) {//查询操作后仓库信息
                        if (strpos($val['next_storage_name'], '/') !== false) {
                            $nextStorCond = [];
                            $nextStorInfo = explode('/', $val['next_storage_name']);
                            if (!empty($nextStorInfo[0])) {
                                $nextStorCond['storage_name'] = $nextStorInfo[0];
                                if (count($nextStorInfo) == 2) {
                                    $nextStorCond['storage_address'] = $nextStorInfo[1];
                                }
                            }
                        } else {
                            $nextStorCond['storage_name'] = $val['next_storage_name'];
                        }
                        if (!empty($nextStorCond['storage_name'])) {
                            $nextStorInfo = StorageModel::where($nextStorCond)
                                ->where([
                                    'main_id' => $mainId,
                                    'delete_flag' => 0
                                ])->first();
                        }
                        if (empty($nextStorInfo)) {
                            $nextStorInfo = $nextStorCond;
                        }

                        if (!empty($nextStorInfo['id']) && !empty($materInfo['id'])) {
                            $nextStorageManageInfo = StorageManageModel::where([
                                'type_id' => $materInfo['id'],
                                'storage_id' => $nextStorInfo['id'],
                                'main_id' => $mainId,
                                'delete_flag' => 0
                            ])->first();
                        }
                    }
                    if ($req['type'] == 1) {//导入入库记录
                        $inStorageData[] = [
                            'type' => 6,
                            'storage_id' => !empty($frontStorInfo['id']) ? $frontStorInfo['id'] : 0,
                            'storage_name' => !empty($frontStorInfo['storage_name']) ? $frontStorInfo['storage_name'] : '',
                            'storage_address' => !empty($frontStorInfo['storage_address']) ? $frontStorInfo['storage_address'] : '',
                            'last_storage_manage_id' => !empty($nextStorageManageInfo['id']) ? $nextStorageManageInfo['id'] : 0,
                            'curr_storage_manage_id' => !empty($frontStorageManageInfo['id']) ? $frontStorageManageInfo['id'] : 0,
                            'materiel_class_id' => !empty($classInfo['id']) ? $classInfo['id'] : 0,
                            'materiel_class_name' => !empty($classInfo['class_name']) ? $classInfo['class_name'] : '',
                            'materiel_class_no' => !empty($classInfo['class_no']) ? $classInfo['class_no'] : '',
                            'materiel_type_id' => !empty($materInfo['id']) ? $materInfo['id'] : 0,
                            'materiel_name' => !empty($materInfo['materiel_name']) ? $materInfo['materiel_name'] : '',
                            'type_name' => !empty($materInfo['type_name']) ? $materInfo['type_name'] : '',
                            'materiel_no' => !empty($materInfo['materiel_no']) ? $materInfo['materiel_no'] : '',
                            'purchase_no' => !empty($val['purchase_no']) ? $val['purchase_no'] : '',
                            'operate_num' => !empty($val['operate_num']) ? $val['operate_num'] : 0,
                            'last_storage_num' => !empty($val['last_storage_num']) ? $val['last_storage_num'] : 0,
                            'next_storage_num' => !empty($val['next_storage_num']) ? $val['next_storage_num'] : 0,
                            'materiel_mean_cost' => !empty($materInfo['cost']) ? $materInfo['cost'] : 0,
                            'serial_number' => !empty($val['serial_number']) ? $val['serial_number'] : '',
                            'remark' => !empty($val['remark']) ? $val['remark'] : '',
                            'all_id_type' => 7,
                            'user_id' => !empty($tokenInfo['userId']) ? $tokenInfo['userId'] : 0,
                            'main_id' => !empty($tokenInfo['mainUserId']) ? $tokenInfo['mainUserId'] : 0,
                            'in_storage_time' => $time,
                            'created_at' => $time
                        ];
                    } else {//导入出库记录
                        $outStorageData[] = [
                            'type' => 7,
                            'storage_id' => !empty($frontStorInfo['id']) ? $frontStorInfo['id'] : 0,
                            'storage_name' => !empty($frontStorInfo['storage_name']) ? $frontStorInfo['storage_name'] : '',
                            'storage_address' => !empty($frontStorInfo['storage_address']) ? $frontStorInfo['storage_address'] : '',
                            'next_storage_id' => !empty($nextStorInfo['id']) ? $nextStorInfo['id'] : '',
                            'next_storage_name' => !empty($nextStorInfo['storage_name']) ? $nextStorInfo['storage_name'] : '',
                            'next_storage_address' => !empty($nextStorInfo['storage_address']) ? $nextStorInfo['storage_address'] : '',
                            'curr_storage_manage_id' => !empty($frontStorageManageInfo['id']) ? $frontStorageManageInfo['id'] : 0,
                            'next_storage_manage_id' => !empty($nextStorageManageInfo['id']) ? $nextStorageManageInfo['id'] : 0,
                            'materiel_class_id' => !empty($classInfo['id']) ? $classInfo['id'] : 0,
                            'materiel_class_name' => !empty($classInfo['class_name']) ? $classInfo['class_name'] : '',
                            'materiel_class_no' => !empty($classInfo['class_no']) ? $classInfo['class_no'] : '',
                            'materiel_type_id' => !empty($materInfo['id']) ? $materInfo['id'] : '',
                            'materiel_name' => !empty($materInfo['materiel_name']) ? $materInfo['materiel_name'] : '',
                            'type_name' => !empty($materInfo['type_name']) ? $materInfo['type_name'] : '',
                            'materiel_no' => !empty($materInfo['materiel_no']) ? $materInfo['materiel_no'] : '',
                            'purchase_no' => !empty($val['purchase_no']) ? $val['purchase_no'] : '',
                            'operate_num' => !empty($val['operate_num']) ? $val['operate_num'] : 0,
                            'last_storage_num' => !empty($val['last_storage_num']) ? $val['last_storage_num'] : 0,
                            'next_storage_num' => !empty($val['next_storage_num']) ? $val['next_storage_num'] : 0,
                            'materiel_mean_cost' => !empty($materInfo['cost']) ? $materInfo['cost'] : 0,
                            'serial_number' => !empty($val['serial_number']) ? $val['serial_number'] : '',
                            'remark' => !empty($val['remark']) ? $val['remark'] : '',
                            'sell_price' => !empty($val['sell_price']) ? $val['sell_price'] : 0,
                            'all_id_type' => 4,
                            'user_id' => !empty($tokenInfo['userId']) ? $tokenInfo['userId'] : 0,
                            'main_id' => !empty($tokenInfo['mainUserId']) ? $tokenInfo['mainUserId'] : 0,
                            'out_storage_time' => $time,
                            'created_at' => $time
                        ];
                    }
                }
            }

            DB::beginTransaction();
            try {
                if ($req['type'] == 1) {
                    if (!empty($inStorageData)) {
                        LogInStorageModel::insert($inStorageData);
                    }
                } else {
                    if (!empty($outStorageData)) {
                        LogOutStorageModel::insert($outStorageData);
                    }
                }
                $logData = '共导入' . ($highestRow - 1) . '条';
                $data['user_id'] = $tokenInfo['userId'];
                $data['main_id'] = $tokenInfo['mainUserId'];
                CommonService::log('导入进入记录', $data, $logData);
                DB::commit();
                unlink($filePath);
                return response()->json(['code' => 0, 'msg' => 'success', 'data' => '']);
            } catch (\Exception $e) {
                DB::rollBack();
                Log::debug('导入进入记录异常' . var_export($e->getMessage(), true));
                return response()->json(['code' => 1, 'msg' => '导入失败', 'data' => '']);
            }
        } else {
            return response()->json(['code' => 1, 'msg' => '无数据', 'data' => '']);
        }
    }

    //处理合并单元格的数据
    private static function fillEmptyMergeCells($sheet)
    {
        $mergeCells = array_values($sheet->getMergeCells());
        $count = count($mergeCells);
        for ($i = 0; $i < $count; $i++) {

            // explode merge cells range,example N25:N27
            $CellIndex = explode(":", $mergeCells[$i]);
            // get main cell value, example N25
            $CellValue = $sheet->getCell($CellIndex[0])->getValue();

            // start index
            $StartIndex = (int)substr($CellIndex[0], 1, 2);
            // end index
            $EndIndex = (int)substr($CellIndex[1], 1, 2);
            // column name example "A" ,"B", max "Z"
            $ColumnName = substr($CellIndex[0], 0, 1);
            // loop to copy the value from main cell to merge cells
            for ($j = $StartIndex; $j <= $EndIndex; $j++) {
                $sheet->setCellValue($ColumnName . $j, $CellValue);
            }
        }
    }

    private static function inLogExport($data, $spreadsheet, $sheet, $keywordCondition)
    {
        $logData = LogService::inStorageExportData($data, $keywordCondition);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "Q");
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '入库时间')
            ->setCellValue('C1', '商品信息')
            ->setCellValue('D1', '销售价')
            ->setCellValue('E1', '成本价')
            ->setCellValue('F1', '序列号')
            ->setCellValue('G1', '操作数量')
            ->setCellValue('H1', '操作后数量')
            ->setCellValue('I1', '操作后仓库')
            ->setCellValue('J1', '入库类型')
            ->setCellValue('K1', '订单编号')
            ->setCellValue('L1', '操作前数量')
            ->setCellValue('M1', '操作前仓库')
            ->setCellValue('N1', '备注')
            ->setCellValue('O1', '标签理由')
            ->setCellValue('P1', '供应商名称')
            ->setCellValue('Q1', '操作时间');
        $goodsNum = [];
        $list = [];
        $totalNum = 0;
        if (!empty($logData)) {
            foreach ($logData as $commonValue) {
                if (!empty($commonValue['content'])) {
                    $goodsNum[] = count($commonValue['content']);

                    $type = '';
                    switch ($commonValue['type']) {
                        case 0:
                            $type = '入库';
                            break;
                        case 1:
                            $type = '调拨入库';
                            break;
                        case 2:
                            $type = '直接入库';
                            break;
                        case 3:
                            $type = '生产订单还库';
                            break;
                        case 4:
                            $type = '扫码入库';
                            break;
                        case 6:
                            $type = '导入入库';
                            break;
                        case 7:
                            $type = '撤销还库';
                            break;
                        case 8:
                            $type = '退换货入库';
                            break;
                    }

                    $allType = '';
                    $purchaseNo = '';
                    switch ($commonValue['all_id_type']) {
                        case 0:
                            $allType = '';
                            break;
                        case 1:
                            $allType = '报溢';
                            break;
                        case 2:
                            $purchaseNo = SalesReturnModel::where('id', $commonValue['all_id'])
                                ->value('order_no');
                            $allType = '退货';
                            break;
                        case 3:
                            $purchaseNo = MaterielModel::where('id', $commonValue['all_id'])
                                ->value('purchase_no');
                            $allType = '采购';
                            break;
                        case 4:
                            $purchaseNo = ProductOrderModel::where('id', $commonValue['all_id'])
                                ->value('product_no');
                            $allType = '生产订单';
                            break;
                        case 9:
                            $purchaseNo = SalesReturnModel::where([
                                'id' => $commonValue['all_id'],
                                ['barter_return_no', '!=', '']
                            ])
                                ->value('order_no');
                            $allType = '退换货入库';
                            break;
                        case 6:
                            $allType = '组件返仓';
                            break;
                    }

                    $sheet->setCellValue('A1', '操作人')
                        ->setCellValue('B1', '入库时间')
                        ->setCellValue('C1', '商品信息')
                        ->setCellValue('D1', '销售价')
                        ->setCellValue('E1', '成本价')
                        ->setCellValue('F1', '序列号')
                        ->setCellValue('G1', '操作数量')
                        ->setCellValue('H1', '操作后数量')
                        ->setCellValue('I1', '操作后仓库')
                        ->setCellValue('J1', '入库类型')
                        ->setCellValue('K1', '订单编号')
                        ->setCellValue('L1', '操作前数量')
                        ->setCellValue('M1', '操作前仓库')
                        ->setCellValue('N1', '备注')
                        ->setCellValue('O1', '标签理由')
                        ->setCellValue('P1', '供应商名称')
                        ->setCellValue('Q1', '操作时间');


                    foreach ($commonValue['content'] as $contentValue) {
                        $list[] = [
                            'nickname' => $commonValue['nickname'],
                            'operate_time' => $commonValue['operate_time'],
                            'materiel_name' => $contentValue['materiel_name'] . '/' . $contentValue['type_name'] . '/' . $contentValue['materiel_no'],
                            'sell_price' => $commonValue['sell_price'],
                            'goods_cost' => $commonValue['goods_cost'],
                            'serial_number' => $contentValue['serial_number'],
                            'operate_num' => $contentValue['operate_num'],
                            'next_storage_num' => $contentValue['next_storage_num'],
                            'next_storage_name' => $contentValue['next_storage_name'] . '/' . $contentValue['shelf_name'] . '/' . $contentValue['location_name'],
                            'type' => $allType . $type,
                            'purchase_no' => $purchaseNo,
                            'last_storage_num' => $contentValue['last_storage_num'],
                            'storage_name' => $contentValue['storage_name'] . '/' . $contentValue['shelf_name'] . '/' . $contentValue['location_name'],
                            'remark' => $contentValue['remark'],
                            'label_name' => $contentValue['label_name'],
                            'supplier_name' => SupplierCustomerModel::query()
                                    ->where('id', $commonValue['supplier_id'])
                                    ->value('name') ?? '',
                            'created_at' => $commonValue['created_at'],
                        ];
                        $totalNum += $contentValue['operate_num'];
                    }
                }
            }
        }

        $cell = ['A', 'B', 'J', 'K', 'P', 'Q'];//需要合并的单元格

        if (!empty($list)) {
            $lineNum = 3;
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }

            foreach ($list as $key => $value) {
                if ($key / 300 == 0) {
                    ob_flush();
                    flush();
                }

                $key += 2;

                $sheet->setCellValue('A' . $key, $value['nickname'])
                    ->setCellValue('B' . $key, $value['operate_time'])
                    ->setCellValue('C' . $key, $value['materiel_name'])
                    ->setCellValue('D' . $key, $value['sell_price'])
                    ->setCellValue('E' . $key, $value['sell_price'])
                    ->setCellValue('F' . $key, $value['serial_number'])
                    ->setCellValue('G' . $key, $value['operate_num'])
                    ->setCellValue('H' . $key, $value['next_storage_num'])
                    ->setCellValue('I' . $key, $value['storage_name'])
                    ->setCellValue('J' . $key, $value['type'])
                    ->setCellValue('K' . $key, $value['purchase_no'])
                    ->setCellValue('L' . $key, $value['last_storage_num'])
                    ->setCellValue('M' . $key, $value['next_storage_name'])
                    ->setCellValue('N' . $key, $value['remark'])
                    ->setCellValue('O' . $key, $value['label_name'])
                    ->setCellValue('P' . $key, $value['supplier_name'])
                    ->setCellValue('Q' . $key, $value['created_at']);
                $lineNum++;
            }

            $lineNum++;
            $sheet->setCellValue("F{$lineNum}", "总计数量：");
            $sheet->setCellValue("G{$lineNum}", $totalNum);
        }
        CommonService::log('入库记录表', $data, '导入入库共' . count($logData) . '条');
        return self::downloadExcel($spreadsheet, '入库记录表_' . date('Y-m-d'), 'Xlsx');
    }

    private static function outLogExport($data, $spreadsheet, $sheet, $keywordCondition)
    {
        $logData = LogService::outStorageExportData($data, $keywordCondition);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "Q");
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '出库时间')
            ->setCellValue('C1', '商品信息')
            ->setCellValue('D1', '销售价')
            ->setCellValue('E1', '成本价')
            ->setCellValue('F1', '序列号')
            ->setCellValue('G1', '操作数量')
            ->setCellValue('H1', '操作后数量')
            ->setCellValue('I1', '操作后仓库')
            ->setCellValue('J1', '出库类型')
            ->setCellValue('K1', '订单编号')
            ->setCellValue('L1', '操作前数量')
            ->setCellValue('M1', '操作前仓库')
            ->setCellValue('N1', '备注')
            ->setCellValue('O1', '标签理由')
            ->setCellValue('P1', '客户名称')
            ->setCellValue('Q1', '操作时间');

        $goodsNum = [];
        $list = [];
        $totalNum = 0;
        if (!empty($logData)) {
            foreach ($logData as $commonValue) {
                if (!empty($commonValue['content'])) {
                    $goodsNum[] = count($commonValue['content']);

                    $type = '';
                    switch ($commonValue['type']) {
                        case 0:
                            $type = '出库';
                            break;
                        case 1:
                            $type = '调拨出库';
                            break;
                        case 2:
                            $type = '直接出库';
                            break;
                        case 3:
                            $type = '生产订单补足';
                            break;
                        case 4:
                            $type = '扫码出库';
                            break;
                        case 7:
                            $type = '导入出库';
                            break;
                        case 8:
                            $type = '撤销出库';
                            break;
                    }

                    $allType = '';
                    $purchaseNo = '';
                    switch ($commonValue['all_id_type']) {
                        case 0:
                            $allType = '';
                            break;
                        case 1:
                            $allType = '报损';
                            break;
                        case 2:
                            $purchaseNo = SellModel::where('id', $commonValue['all_id'])
                                ->value('order_no');
                            $allType = '销售';
                            break;
                        case 3:
                            $purchaseNo = ProductOrderModel::where('id', $commonValue['all_id'])
                                ->value('product_no');
                            $allType = '生产订单出库';
                            break;
                    }

                    foreach ($commonValue['content'] as $contentValue) {
                        $list[] = [
                            'nickname' => $commonValue['nickname'],
                            'operate_time' => $commonValue['operate_time'],
                            'materiel_name' => $contentValue['materiel_name'] . '/' . $contentValue['type_name'] . '/' . $contentValue['materiel_no'],
                            'sell_price' => $contentValue['sell_price'],
                            'goods_cost' => $contentValue['goods_cost'],
                            'serial_number' => $contentValue['serial_number'],
                            'operate_num' => $contentValue['operate_num'],
                            'next_storage_num' => $contentValue['next_storage_num'],
                            'next_storage_name' => $contentValue['next_storage_name'] . '/' . $contentValue['shelf_name'] . '/' . $contentValue['location_name'],
                            'type' => $allType . $type,
                            'purchase_no' => $purchaseNo,
                            'last_storage_num' => $contentValue['last_storage_num'],
                            'storage_name' => $contentValue['storage_name'] . '/' . $contentValue['shelf_name'] . '/' . $contentValue['location_name'],
                            'remark' => $contentValue['remark'],
                            'label_name' => $contentValue['label_name'],
                            'supplier_name' => SupplierCustomerModel::query()
                                    ->where('id', $commonValue['supplier_id'])
                                    ->value('name') ?? '',
                            'created_at' => $commonValue['created_at'],
                        ];
                        $totalNum += $contentValue['operate_num'];
                    }
                }
            }
        }

        $cell = ['A', 'B', 'J', 'M', 'P', 'Q'];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
            $lineNum = 3;
            foreach ($list as $key => $value) {
                if ($key / 300 == 0) {
                    ob_flush();
                    flush();
                }
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['nickname'])
                    ->setCellValue('B' . $key, $value['operate_time'])
                    ->setCellValue('C' . $key, $value['materiel_name'])
                    ->setCellValue('D' . $key, (float)$value['sell_price'])
                    ->setCellValue('E' . $key, (float)$value['goods_cost'])
                    ->setCellValue('F' . $key, $value['serial_number'])
                    ->setCellValue('G' . $key, $value['operate_num'])
                    ->setCellValue('H' . $key, $value['next_storage_num'])
                    ->setCellValue('I' . $key, $value['storage_name'])
                    ->setCellValue('J' . $key, $value['type'])
                    ->setCellValue('K' . $key, $value['purchase_no'])
                    ->setCellValue('L' . $key, $value['last_storage_num'])
                    ->setCellValue('M' . $key, $value['next_storage_name'])
                    ->setCellValue('N' . $key, $value['remark'])
                    ->setCellValue('O' . $key, $value['label_name'])
                    ->setCellValue('P' . $key, $value['supplier_name'])
                    ->setCellValue('Q' . $key, $value['created_at']);
                $lineNum++;
            }
            $lineNum++;
            $sheet->setCellValue("E{$lineNum}", "总计数量：");
            $sheet->setCellValue("F{$lineNum}", $totalNum);
        }
        CommonService::log('出库记录表', $data, '导入出库共' . count($logData) . '条');
        return self::downloadExcel($spreadsheet, '出库记录表_' . date('Y-m-d'), 'Xlsx');
    }

    private static function inGoodsExport($data, $spreadsheet, $sheet, $keywordCondition)
    {
        $title='供应商';
        if($data['type'] ==1){
            $title='客户';
        $logData = LogService::outGoodsExportData($data, $keywordCondition);
        }else{
        $logData = LogService::inGoodsExportData($data, $keywordCondition);
        }
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "C");
        $sheet->setCellValue('A1', '客户')
            ->setCellValue('B1', '商品(规格)')
            ->setCellValue('C1', '数量');
        $goodsNum = [];
        $list = [];
        if (!empty($logData)) {
            foreach ($logData as $val) {
                $goodsNum[] = count($val['goods_list']);
                if (!empty($val['goods_list'])) {
                    foreach ($val['goods_list'] as $gVal) {
                        $list[] = $gVal;
                    }
                }
            }
        }
        $cell = ['A'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['name'])
                    ->setCellValue('B' . $key, $value['materiel_name'] . '（' . $value['type_name'] . '）')
                    ->setCellValue('C' . $key, '共' . $value['total_num'] . $value['materiel_unit']);
            }
        }
        CommonService::log('入库'.$title.'记录表', $data, '导入入库共' . count($logData) . '条');
        return self::downloadExcel($spreadsheet, '入库'.$title.'记录表_' . date('Y-m-d'), 'Xlsx');
    }



    /**
     * 出入库操作记录导出 ----v3.2.6废弃
     * @param Request $request
     * @return bool|JsonResponse
     * @Author: Sun Wu
     * @Created at: 2022/10/29 17:15
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     *
     */
    public function storageStartExportAbandon(Request $request)
    {
        $token = $request->header('token');
        $userInfo = self::checkToken($token);

        $validator = Validator::make($data = $request->all(), [
            'type' => [
                'required',
                'integer',
                Rule::In([0, 1, 2]),
            ],//操作日志类型;0:入库;1:出库;2:套餐出库;
            'status' => [
                'required',
                'integer',
                Rule::In([0, 1]),
            ],//撤销状态(0:未撤销;1:已撤销;)
        ],
            [
                'type.required' => '参数`日志类型`缺失',
                'type.integer' => '参数`日志类型`只能为数字',
                'type.in' => '参数`日志类型`不合法',
            ]
        );

        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => '',
            ]);
        }

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('出入库记录报表');
        if (!$userInfo['userType']) {
            $user['a.main_id'] = $userInfo['mainUserId'];
        } else {
            $user['a.user_id'] = $userInfo['userId'];
        }

        switch ($data['type']) {
            case 0:
                self::inLogExport($data, $user, $spreadsheet, $sheet);
                break;
            case 1:
                self::outLogExport($data, $user, $spreadsheet, $sheet);
                break;
            default:
                self::packageLogExport($data, $user, $spreadsheet, $sheet);
        }

        return true;
    }

    //废弃
    private function inLogExportAbandon(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $logData = LogService::inLogExport($data, $user);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "W");
        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '商品名')
            ->setCellValue('C1', '商品规格')
            ->setCellValue('D1', '商品成本')
            ->setCellValue('E1', '序列号')
            ->setCellValue('F1', '批次号')
            ->setCellValue('G1', '上一批次号')
            ->setCellValue('H1', '调拨编号')
            ->setCellValue('I1', '当前仓库名')
            ->setCellValue('J1', '当前仓库地址')
            ->setCellValue('K1', '当前货架')
            ->setCellValue('L1', '当前库位')
            ->setCellValue('M1', '上一仓库名')
            ->setCellValue('N1', '上一仓库地址')
            ->setCellValue('O1', '上一货架')
            ->setCellValue('P1', '上一库位')
            ->setCellValue('Q1', '操作前数量')
            ->setCellValue('R1', '操作数量')
            ->setCellValue('S1', '操作后数量')
            ->setCellValue('T1', '类型')
            ->setCellValue('U1', '操作人')
            ->setCellValue('V1', '出库时间')
            ->setCellValue('W1', '操作时间');

        foreach ($logData as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A' . $key, $value['materiel_img'])
                ->setCellValue('B' . $key, $value['materiel_name'])
                ->setCellValue('C' . $key, $value['type_name'])
                ->setCellValue('D' . $key, $value['materiel_mean_cost'])
                ->setCellValue('E' . $key, empty($value['serial_number']) ? '' : $value['serial_number'])
                ->setCellValue('F' . $key, empty($value['batch_no']) ? '' : $value['batch_no'])
                ->setCellValue('G' . $key, empty($value['last_batch_no']) ? '' : $value['last_batch_no'])
                ->setCellValue('H' . $key, empty($value['allocate_no']) ? '' : $value['allocate_no'])
                ->setCellValue('I' . $key, $value['storage_name'])
                ->setCellValue('J' . $key, $value['storage_address'])
                ->setCellValue('K' . $key, $value['shelf_name'])
                ->setCellValue('L' . $key, $value['location_name'])
                ->setCellValue('M' . $key, empty($value['last_storage_name']) ? '' : $value['last_storage_name'])
                ->setCellValue('N' . $key, empty($value['last_storage_address']) ? '' : $value['last_storage_address'])
                ->setCellValue('O' . $key, empty($value['last_shelf_name']) ? '' : $value['last_shelf_name'])
                ->setCellValue('P' . $key, empty($value['last_location_name']) ? '' : $value['last_location_name'])
                ->setCellValue('Q' . $key, $value['last_storage_num'])
                ->setCellValue('R' . $key, $value['operate_num'])
                ->setCellValue('S' . $key, $value['next_storage_num'])
                ->setCellValue('T' . $key, $value['type'])
                ->setCellValue('U' . $key, $value['nickname'])
                ->setCellValue('V' . $key, $value['in_storage_time'])
                ->setCellValue('W' . $key, $value['created_at']);

            switch ($value['type']) {
                case 0:
                    $value['type'] = '入库';
                    break;
                case 1:
                    $value['type'] = '调拨入库';
                    break;
                case 2:
                    $value['type'] = '直接入库';
                    break;
            }
            $sheet->setCellValue('R' . $key, $value['type']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '入库操作记录报表_' . date('Y-m-d'), 'Xlsx');
        return true;
    }

    private function outLogExportAbandon(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $logData = LogService::outLogExport($data, $user);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "X");
        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '商品名')
            ->setCellValue('C1', '商品规格')
            ->setCellValue('D1', '商品编号')
            ->setCellValue('E1', '商品成本')
            ->setCellValue('F1', '序列号')
            ->setCellValue('G1', '批次号')
            ->setCellValue('H1', '上一批次号')
            ->setCellValue('I1', '调拨编号')
            ->setCellValue('J1', '当前仓库名')
            ->setCellValue('K1', '当前仓库地址')
            ->setCellValue('L1', '当前库位')
            ->setCellValue('M1', '当前货架')
            ->setCellValue('N1', '下一仓库名')
            ->setCellValue('O1', '下一仓库地址')
            ->setCellValue('P1', '下一库位')
            ->setCellValue('Q1', '下一货架')
            ->setCellValue('R1', '操作前数量')
            ->setCellValue('S1', '操作数量')
            ->setCellValue('T1', '操作后数量')
            ->setCellValue('U1', '类型')
            ->setCellValue('V1', '出库时间')
            ->setCellValue('W1', '操作人')
            ->setCellValue('X1', '操作时间');

        foreach ($logData as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A' . $key, $value['materiel_img'])
                ->setCellValue('B' . $key, $value['materiel_name'])
                ->setCellValue('C' . $key, $value['type_name'])
                ->setCellValue('D' . $key, $value['materiel_no'])
                ->setCellValue('E' . $key, $value['materiel_mean_cost'])
                ->setCellValue('F' . $key, empty($value['serial_number']) ? '' : $value['serial_number'])
                ->setCellValue('G' . $key, empty($value['batch_no']) ? '' : $value['batch_no'])
                ->setCellValue('H' . $key, empty($value['before_allocate_batch_no']) ? '' : $value['before_allocate_batch_no'])
                ->setCellValue('I' . $key, empty($value['allocate_no']) ? '' : $value['allocate_no'])
                ->setCellValue('J' . $key, $value['storage_name'])
                ->setCellValue('K' . $key, $value['storage_address'])
                ->setCellValue('L' . $key, $value['shelf_name'])
                ->setCellValue('M' . $key, $value['location_name'])
                ->setCellValue('N' . $key, empty($value['next_storage_name']) ? '' : $value['next_storage_name'])
                ->setCellValue('O' . $key, empty($value['next_storage_address']) ? '' : $value['next_storage_address'])
                ->setCellValue('P' . $key, empty($value['next_shelf_name']) ? '' : $value['next_shelf_name'])
                ->setCellValue('Q' . $key, empty($value['next_location_name']) ? '' : $value['next_location_name'])
                ->setCellValue('R' . $key, $value['last_storage_num'])
                ->setCellValue('S' . $key, $value['operate_num'])
                ->setCellValue('T' . $key, $value['next_storage_num'])
                ->setCellValue('V' . $key, $value['nickname'])
                ->setCellValue('W' . $key, $value['out_storage_time'])
                ->setCellValue('X' . $key, $value['created_at']);

            switch ($value['type']) {
                case 0:
                    $value['type'] = '出库';
                    break;
                case 1:
                    $value['type'] = '调拨出库';
                    break;
                case 2:
                    $value['type'] = '直接出库';
                    break;
            }
            $sheet->setCellValue('U' . $key, $value['type']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '出库操作记录报表_' . date('Y-m-d'), 'Xlsx');
        return true;
    }

    private function packageLogExport(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $getPackageId = LogService::getPackageId($data, $user);

        $packageId = [];
        foreach ($getPackageId as $value) {
            $packageId[] = $value['id'];
        }

        $logData = LogService::packageOutLogExport($packageId);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "Q");
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '套餐名')
            ->setCellValue('C1', '套餐操作数量')
            ->setCellValue('D1', '商品图')
            ->setCellValue('E1', '商品名')
            ->setCellValue('F1', '规格名')
            ->setCellValue('G1', '出库仓库名')
            ->setCellValue('H1', '出库仓库地址')
            ->setCellValue('I1', '出库货架')
            ->setCellValue('J1', '出库库位')
            ->setCellValue('K1', '出库前数量')
            ->setCellValue('L1', '商品出库数量')
            ->setCellValue('M1', '出库后数量')
            ->setCellValue('N1', '批次号')
            ->setCellValue('O1', '备注')
            ->setCellValue('P1', '出库时间')
            ->setCellValue('Q1', '操作时间');

        $cell = ['A', 'B', 'C', 'O', 'P', 'Q'];//需要合并的单元格

        foreach ($cell as $item) {
            $start = 2;
            foreach ($logData['data2'] as $k => $value) {
                $end = ($start + $value) - 1;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                $start = $end + 1;
            }
        }

        foreach ($logData['data1'] as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A' . $key, $value['nickname'])
                ->setCellValue('B' . $key, $value['package_name'])
                ->setCellValue('C' . $key, $value['num'])
                ->setCellValue('D' . $key, $value['materiel_img'])
                ->setCellValue('E' . $key, $value['materiel_name'])
                ->setCellValue('F' . $key, $value['type_name'])
                ->setCellValue('G' . $key, $value['storage_name'])
                ->setCellValue('H' . $key, $value['storage_address'])
                ->setCellValue('I' . $key, $value['shelf_name'])
                ->setCellValue('J' . $key, $value['location_name'])
                ->setCellValue('K' . $key, $value['last_storage_num'])
                ->setCellValue('L' . $key, $value['operate_num'])
                ->setCellValue('M' . $key, $value['next_storage_num'])
                ->setCellValue('N' . $key, empty($value['batch_no']) ? '' : $value['batch_no'])
                ->setCellValue('O' . $key, empty($value['remark']) ? '' : $value['remark'])
                ->setCellValue('P' . $key, empty($value['out_storage_time']) ? '' : $value['out_storage_time'])
                ->setCellValue('Q' . $key, empty($value['created_at']) ? '' : $value['created_at']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '套餐出库操作记录报表_' . date('Y-m-d'), 'Xlsx');
        return true;
    }

    /**
     * 物料分类导入（zlf）
     * @param Request $request
     */
    public function goodClassImport(Request $request)
    {
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code']))
            return fail('', $tokenInfo['msg']);
        $mainId = $tokenInfo['mainUserId'];
        $files = $request->file('class_temp');
        $ext = $files->extension();
        if ($files->getSize() > 10 * 1024 * 1024) {
            return fail('', '文件大小不能超出10M');
        }

        if (!in_array($ext, ['xls', 'xlsx'])) {
            $ext = $files->clientExtension();
            if (!in_array($ext, ['xls', 'xlsx'])) {
                return response()->json([
                    'code' => 1,
                    'msg' => '必须为excel表格，且必须为xlsx或xls格式！',
                    'data' => ''
                ]);
            }
        }
        if ($ext == 'xls') {
            $reader = IOFactory::createReader('Xls');
        } else if ($ext == 'xlsx') {
            $reader = IOFactory::createReader('Xlsx');
        }
        $path = $files->store(date('Y') . '/' . date('m') . '/' . date('d'));
        $filePath = public_path() . '/uploads/' . $path;
        $objPHPExcel = $reader->load($filePath);
        $excelSheet = $objPHPExcel->getSheet(0);//读取第一个工作表
        SELF::fillEmptyMergeCells($excelSheet);//处理合并单元格的数据
        $highestColumn = $excelSheet->getHighestColumn();//取得总列数
        $highestRow = $excelSheet->getHighestRow();// 取得总行数
        $data = $excelSheet->toArray();
        if (empty($data[1]))
            return fail('', '没有要导入的数据');
        $i = 1;
        DB::beginTransaction();
        try {
            $collection = collect($data);
            $flattened = $collection->flatMap(function (array $values, int $key) use ($mainId, $i) {
                if ($key > 0) {
                    if (!empty($values[0]) && !empty($values[1])) {
                        $friClassName = Str::beforeLast($values[0], '(') ?? '';
                        if (empty($friClassName))
                            $friClassName = Str::beforeLast($values[0], '（') ?? '';
                        $friClassNo = Str::between($values[0], '(', ')') ?? '';
                        if (empty($friClassNo))
                            $friClassNo = Str::between($values[0], '（', '）') ?? '';
                        $secClassName = Str::beforeLast($values[1], '(') ?? '';
                        if (empty($secClassName))
                            $secClassName = Str::beforeLast($values[1], '（') ?? '';
                        $secClassNo = Str::between($values[1], '(', ')') ?? '';
                        if (empty($secClassNo))
                            $secClassNo = Str::between($values[1], '（', '）') ?? '';
                        $goodsName = Str::beforeLast($values[3], '/') ?? '';
                        $goodsNo = Str::afterLast($values[3], '/') ?? '';
                        $typeName = $values[5];
                        $friClassCond = [];

                        if (!empty($friClassName) && !empty($friClassNo)) {//一级分类名称和编号都不为空
                            $friClassCond = ['class_no' => $friClassNo];
                        } elseif (empty($friClassName)) {//一级分类名称为空
                            throw new \Exception('第' . $i . '行,一级分类名称不能为空');
                        }
                        $friClassCond = Arr::add($friClassCond, 'class_name', $friClassName);
                        $friClassInfo = MaterielClassModel::where([
                            'parent_id' => 0,
                            'main_id' => $mainId,
                            'delete_flag' => 0,
                            'is_active' => 1
                        ])->where($friClassCond)->first();
                        $secClassCond = [];
                        if (!empty($secClassName) && !empty($secClassNo))//二级分类名称和编号都不为空
                            $secClassCond = ['class_no' => $secClassNo];
                        if (!empty($secClassName)) {
                            $secClassCond = Arr::add($secClassCond, 'class_name', $secClassName);
                            $query = MaterielClassModel::where([
                                'main_id' => $mainId,
                                'delete_flag' => 0,
                                'is_active' => 1
                            ])->where($secClassCond)
                                ->where('parent_id', '!=', 0);
                            if (!empty($friClassInfo))
                                $query->where('parent_id', $friClassInfo['id']);
                            $secClassInfo = $query->first();
                        }
                        $goodsCond = [];
                        if (!empty($goodsName) && !empty($goodsNo) && !empty($typeName)) {//商品名称、编号、类型都不为空
                            $goodsCond = ['materiel_no' => $goodsNo, 'type_name' => $typeName];
                        } elseif (!empty($goodsName) && !empty($goodsNo) && empty($typeName)) {//商品名称、编号不为空,类型为空
                            $goodsCond = ['materiel_no' => $goodsNo];
                        } elseif (!empty($goodsName) && empty($goodsNo) && !empty($typeName)) {//商品名称、类型不为空,编号为空
                            $goodsCond = ['type_name' => $typeName];
                        }
                        if (!empty($goodsName) && !empty($secClassName)) {
                            $goodsCond = Arr::add($goodsCond, 'materiel_name', $goodsName);
                            $query = MaterielTypeModel::where([
                                'main_id' => $mainId,
                                'delete_flag' => 0
                            ])->where($goodsCond);
                            if (!empty($secClassInfo))
                                $query->where('materiel_class_id', $secClassInfo['id']);
                            $goodsInfo = $query->first();
                            if (!empty($goodsInfo))
                                throw new \Exception('第' . $i . '行,商品名称为' . $goodsName . '已存在');
                        }
                        $isProduct = 0;
                        if (!empty($values[2])) {
                            if ($values[2] == '套餐') {
                                $isProduct = 1;
                            }
                        }
                        if (empty($friClassInfo) && !empty($friClassName)) {
                            $friClassCond = Arr::add($friClassCond, 'main_id', $mainId);
                            $friClassCond = Arr::add($friClassCond, 'is_product_class', $isProduct);
                            $friClassInfo['id'] = MaterielClassModel::insertGetId($friClassCond);
                        }
                        if (empty($secClassInfo) && !empty($secClassName)) {
                            $secClassCond = Arr::add($secClassCond, 'main_id', $mainId);
                            $secClassCond = Arr::add($secClassCond, 'parent_id', $friClassInfo['id']);
                            $secClassCond = Arr::add($secClassCond, 'is_product_class', $isProduct);
                            $secClassInfo['id'] = MaterielClassModel::insertGetId($secClassCond);
                        }
                        if (empty($goodsInfo) && !empty($goodsName) && !empty($secClassInfo)) {
                            $goodsData = [];
                            $goodsData['materiel_name'] = $goodsName;
                            $goodsData['type_name'] = $typeName ?? '';
                            $goodsData['materiel_no'] = $goodsNo ?? '';
                            $goodsData['materiel_unit'] = $values[6] ?? '';
                            $goodsData['alert_num'] = $values[7] ?? 0;
                            $goodsData['length'] = $values[8] ?? 0;
                            $goodsData['width'] = $values[9] ?? 0;
                            $goodsData['height'] = $values[10] ?? 0;
                            $goodsData['weight'] = $values[11] ?? 0;
                            $goodsData['bar_code'] = $values[12] ?? '';
                            $goodsData['number_prefix'] = $values[13] ?? '';
                            $goodsData['texture'] = $values[14] ?? '';
                            $goodsData['executive_standard'] = $values[15] ?? '';
                            $goodsData['ratio'] = $values[16] ?? 0;
                            $goodsData['calc_ratio'] = $values[17] ?? 0;
                            $goodsData['cost'] = $values[18] ?? 0;
                            $goodsData['selling_price'] = $values[19] ?? 0;
                            $goodsData['lower_selling_price'] = $values[20] ?? 0;
                            $goodsData['remark'] = $values[22] ?? '';
                            $goodsData['main_id'] = $mainId;
                            $goodsData['materiel_class_id'] = $secClassInfo['id'];
                            $goodsData['is_product_materiel'] = $isProduct;
                            $goodsData['created_at'] = Carbon::now()->timestamp;
                            MaterielTypeModel::insertGetId($goodsData);
                        }
                        $i++;
                    }
                }
            });
            $flattened->all();
            DB::commit();
            unlink($filePath);
            $data['main_id'] = $tokenInfo['mainUserId'];
            $data['user_id'] = $tokenInfo['userId'];
            CommonService::log('导入分类', $data, '导入共' . (count($data) - 1) . '条');
            return response()->json(['code' => 0, 'msg' => 'success', 'data' => '']);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::debug('导入分类信息excel异常:' . var_export($e->getMessage(), true));
            return response()->json(['code' => 1, 'msg' => $e->getMessage(), 'data' => '']);
        }
    }

    /**
     * 导出excel-分类导出
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2023/9/16 11:34
     * @return array|void
     */
    public function materielClassExport(Request $request)
    {
        $data = $request->all();
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        /* $tokenInfo['mainUserId'] = 6588;
        $tokenInfo['userId'] = 6588;*/
        //获取导出的数据
        $exportData = MaterielClassService::classListExport($tokenInfo['mainUserId'], $data);

        @ini_set('memory_limit', '1024M');
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('商品分类报表');
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:W1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(60);
        $spreadsheet->getActiveSheet()->getStyle('A:W')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:W')->getAlignment()->setVertical('center');
        $e = "W";
        if ($data['export_flag'] == 2) $e = "B";
        //设置列宽
        for ($column = "A"; $column <= $e; $column++) {
            $spreadsheet->getActiveSheet()->getColumnDimension($column)->setWidth(30);
        }
        //设置自动换行
        for ($column = "A"; $column <= $e; $column++) {
            $spreadsheet->getActiveSheet()->getStyle($column)->getAlignment()->setWrapText(true);
        }
        $sheet->setCellValue('A1', '一级分类（编号）')
            ->setCellValue('B1', '二级分类（编号）');
        if ($data['export_flag'] == 1)
            $sheet->setCellValue('C1', '商品种类（套餐/商品）')
                ->setCellValue('D1', '商品名/编号')
                ->setCellValue('E1', '第二名称')
                ->setCellValue('F1', '类型')
                ->setCellValue('G1', '单位')
                ->setCellValue('H1', '预警数')
                ->setCellValue('I1', '长(cm)')
                ->setCellValue('J1', '宽(cm)')
                ->setCellValue('K1', '高(cm)')
                ->setCellValue('L1', '重量(kg)')
                ->setCellValue('M1', '条形码')
                ->setCellValue('N1', '编号前缀')
                ->setCellValue('O1', '材质')
                ->setCellValue('P1', '执行标准')
                ->setCellValue('Q1', '折扣比例')
                ->setCellValue('R1', '比例(用于计算套餐内商品价格)')
                ->setCellValue('S1', '成本')
                ->setCellValue('T1', '销售价格')
                ->setCellValue('U1', '最低销售价格')
                ->setCellValue('V1', '套餐组件')
                ->setCellValue('W1', '备注');
        $list = [];
        $goodsNum = [];
        if (isset($exportData['code']) || empty($exportData)) {
            return SELF::downloadExcel($spreadsheet, '商品分类报表_' . date('Y-m-d'), 'Xlsx');
        }

        if ($data['export_flag'] == 1) {//导出商品:导出选中类目的商品
            foreach ($exportData as $fVal) {
                foreach ($fVal['next_class'] as $sVal) {
                    if (!empty($sVal['next_type'])) {
                        $goodsNum[] = count($sVal['next_type']);
                        foreach ($sVal['next_type'] as $item) {
                            //二级分类下面的商品
                            $type = '商品';
                            $module = '';
                            if (!empty($item['is_product_materiel'])) {
                                $condition = [];
                                $condition['product_id'] = $item['id'];
                                $condition['b.main_id'] = $item['main_id'];
                                $productContentList = MaterielProductContentModel::select(
                                    'materiel_name',
                                    'type_name',
                                    'materiel_no',
                                    'price',
                                    'num',
                                    'materiel_unit'
                                )->from('materiel_product_content as a')
                                    ->leftJoin('materiel_type as b', 'b.id', '=', 'a.type_id')
                                    ->where($condition)
                                    ->where('a.delete_flag', 0)
                                    ->where('b.delete_flag', 0)
                                    ->get()->toArray();
                                $type = '套餐';
                                if (!empty($productContentList)) {
                                    $p = 1;
                                    foreach ($productContentList as $pVal) {
                                        $module .= "组件" . $p . "：" . $pVal['materiel_name'] . "（" . $pVal['type_name'] . "）" .
                                            " 销售价格：" . floatval($pVal['price']) . "元 单位：" . floatval($pVal['num']) .
                                            $pVal['materiel_unit'] . "\r\n";
                                        $p++;
                                    }
                                }
                            }
                            $list[] = [
                                'first_class_name' => $fVal['class_name'] . '(' . $fVal['class_no'] . ')',//一级分类名称和编号
                                'second_class_name' => $sVal['class_name'] . '(' . $sVal['class_no'] . ')',//二级分类名称和编号
                                'goods_type' => $type,//商品种类
                                'goods_name' => $item['materiel_name'] . '/' . $item['materiel_no'],//商品名称
                                'two_name' => $item['two_materiel_name'],//第二商品名称
                                'type_name' => $item['type_name'],//商品类型
                                'materiel_unit' => $item['materiel_unit'], //商品单位
                                'alert_num' => $item['alert_num'],//商品总预警数
                                'length' => $item['length'],//长(cm)
                                'width' => $item['width'],//宽(cm)
                                'height' => $item['height'],//高(cm)
                                'weight' => $item['weight'],//重量(kg)
                                'bar_code' => $item['bar_code'] . ' ',//条形码
                                'number_prefix' => $item['number_prefix'],//编号前缀
                                'texture' => $item['texture'],//材质
                                'executive_standard' => $item['executive_standard'],//执行标准
                                'ratio' => $item['ratio'],//真实折扣比例
                                'calc_ratio' => $item['calc_ratio'],//比例(用于计算套餐内商品价格)
                                'cost' => $item['cost'],//成本
                                'selling_price' => $item['selling_price'],//销售价
                                'lower_selling_price' => $item['lower_selling_price'],//最低销售价
                                'tc_module' => $module,//套餐组件集
                                'remark' => $item['remark'],//备注
                            ];
                        }
                    }
                }
            }
        } else {//导出类目:导出所有类目
            foreach ($exportData as $fkey => $fVal) {
                if (!empty($fVal['next_class'])) {//有二级分类导出
                    $goodsNum[] = count($fVal['next_class']);
                    foreach ($fVal['next_class'] as $sVal) {
                        $list[] = [
                            'first_class_name' => $fVal['class_name'] . '（' . $fVal['class_no'] . '）',//一级分类名称和编号
                            'second_class_name' => $sVal['class_name'] . '（' . $sVal['class_no'] . '）',//二级分类名称和编号
                        ];
                    }
                } else {//没有二级分类的一级分类导出
                    $list[] = [
                        'first_class_name' => $fVal['class_name'] . '（' . $fVal['class_no'] . '）',//一级分类名称和编号
                        'second_class_name' => '',//二级分类名称和编号
                    ];
                }
            }
        }
        $cell = ['A', 'B'];//需要合并的单元格
        if ($data['export_flag'] == 2) $cell = ['A'];
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
            foreach ($list as $key => $value) {
                $key += 2;
                $i = 0;
                $keyArr = array_keys($value);
                for ($column = "A"; $column <= $e; $column++) {
                    $sheet->setCellValue($column . $key, $value[$keyArr[$i]]);
                    $i++;
                }
            }
        }

        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('商品分类报表', $data, '导出共' . count($list) . '条');

        return SELF::downloadExcel($spreadsheet, '商品分类报表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     * 物料分类模板导出（zlf）
     * @param Request $request
     */
    public function classTempDownload()
    {
        ob_end_clean();
        $headers = [
            'Content-Type: application/xlsx',
        ];
        return response()->download(public_path() . '/class_temp.xlsx', 'class_temp.xlsx', $headers);
    }

    /**
     * 导出excel-销售记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/10 10:48
     * @return array|void
     */
    public function barterReturnExport(Request $request)
    {
        $token = $request->header('token');
        $data = $request->all();

        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        /*  $tokenInfo['mainUserId'] = 6588;
        $tokenInfo['userId'] = 6588; */
        if ($tokenInfo['userId'] == $tokenInfo['mainUserId']) {
            $condition['main_id'] = $tokenInfo['mainUserId'];
        } else {
            $condition['user_id'] = $tokenInfo['userId'];
        }
        $data['user_id'] = $tokenInfo['userId'];
        $data['main_id'] = $tokenInfo['mainUserId'];
        //获取导出的数据
        $exportData = BarterService::barterReturnList($condition, $data);
        /*   echo "<pre>";
        var_dump($exportData);die; */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('退换货记录表');
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "BA");
        $sheet->setCellValue('A1', '操作人昵称')
            ->setCellValue('B1', '操作人账号')
            ->setCellValue('C1', '客户')
            ->setCellValue('D1', '审批人')
            ->setCellValue('E1', '创建时间')
            ->setCellValue('F1', '外部订单')
            ->setCellValue('G1', '差价（元）')
            ->setCellValue('H1', '付款类型')
            ->setCellValue('I1', '付款金额（元）')
            ->setCellValue('J1', '付款状态')
            ->setCellValue('K1', '付款账号')
            ->setCellValue('L1', '付款时间')
            ->setCellValue('M1', '总备注')
            ->setCellValue('N1', '退货-订单号')
            ->setCellValue('O1', '入库状态')
            ->setCellValue('P1', '收货人')
            ->setCellValue('Q1', '发货方式')
            ->setCellValue('R1', '运输方式')
            ->setCellValue('S1', '发货时间')
            ->setCellValue('T1', '备注信息')
            ->setCellValue('U1', '物流信息')
            ->setCellValue('V1', '物流公司')
            ->setCellValue('W1', '物流单号')
            ->setCellValue('X1', '商品信息')
            ->setCellValue('Y1', '商品名称（名称/编号）')
            ->setCellValue('Z1', '入库仓库（仓库/货架/库位）')
            ->setCellValue('AA1', '成本价格（元）')
            ->setCellValue('AB1', '销售价格（元）')
            ->setCellValue('AC1', '操作数量')
            ->setCellValue('AD1', '入库数量')
            ->setCellValue('AE1', '序列号')
            ->setCellValue('AF1', '备注')
            ->setCellValue('AG1', '退货总金额（元）')
            ->setCellValue('AH1', '换货-订单号')
            ->setCellValue('AI1', '出库状态')
            ->setCellValue('AJ1', '收货人')
            ->setCellValue('AK1', '发货方式')
            ->setCellValue('AL1', '运输方式')
            ->setCellValue('AM1', '发货时间')
            ->setCellValue('AN1', '备注信息')
            ->setCellValue('AO1', '物流信息')
            ->setCellValue('AP1', '物流公司')
            ->setCellValue('AQ1', '物流单号')
            ->setCellValue('AR1', '商品信息')
            ->setCellValue('AS1', '商品名称（名称/编号）')
            ->setCellValue('AT1', '入库仓库（仓库/货架/库位）')
            ->setCellValue('AU1', '成本价格（元）')
            ->setCellValue('AV1', '销售价格（元）')
            ->setCellValue('AW1', '操作数量')
            ->setCellValue('AX1', '入库数量')
            ->setCellValue('AY1', '序列号')
            ->setCellValue('AZ1', '备注')
            ->setCellValue('BA1', '换货总金额（元）');
        $list = [];
        $goodsNum = [];
        if (!empty($exportData)) {
            foreach ($exportData as $key => $value) {
                $forNum = count($value['return_goods_detail_info']);
                if (count($value['barter_goods_detail_info']) > count($value['return_goods_detail_info'])) {
                    $forNum = count($value['barter_goods_detail_info']);
                }
                $barLogisticsList = BarterLogisticsInfoModel::where([
                    'delete_flag' => 0,
                    'main_id' => $data['main_id'],
                    'barter_id' => $value['barter_id']
                ])->get()->toArray();

                $retLogisticsList = SalesReturnLogisticsInfoModel::where([
                    'delete_flag' => 0,
                    'main_id' => $data['main_id'],
                    'return_id' => $value['return_id']
                ])->get()->toArray();
                if (count($barLogisticsList) > $forNum) {
                    $forNum = count($barLogisticsList);
                }
                if (count($retLogisticsList) > $forNum) {
                    $forNum = count($retLogisticsList);
                }
                if (!empty($forNum)) {//商品信息
                    /* echo "<br/>----<br/>";
                    echo '换货id:'.$value['barter_id'].'退货id:'.$value['return_id'].'换货商品个数：'.count($value['return_goods_detail_info']).'退货商品个数:'.count($value['return_goods_detail_info']).'换货物流个数：'.count($barLogisticsList).'退货物流个数：'.count($retLogisticsList).'最后for个数：'.$forNum; */
                    $goodsNum[] = $forNum;
                    $barDeliverGoodsTypeMsg = '';
                    if ($value['deliver_goods_type'] == 1) {
                        $barDeliverGoodsTypeMsg = '自提';
                    } elseif ($value['deliver_goods_type'] == 2) {
                        $barDeliverGoodsTypeMsg = '代发';
                    } elseif ($value['deliver_goods_type'] == 3) {
                        $barDeliverGoodsTypeMsg = '送货';
                    }
                    $barTransportTypeMsg = '';
                    if ($value['transport_type'] == 1) {
                        $barTransportTypeMsg = '空运';
                    } elseif ($value['transport_type'] == 2) {
                        $barTransportTypeMsg = '海运';
                    } elseif ($value['transport_type'] == 3) {
                        $barTransportTypeMsg = '铁路';
                    } elseif ($value['transport_type'] == 4) {
                        $barTransportTypeMsg = '公路';
                    }
                    $retDeliverGoodsTypeMsg = '';
                    if ($value['deliver_goods_type'] == 1) {
                        $retDeliverGoodsTypeMsg = '自提';
                    } elseif ($value['deliver_goods_type'] == 2) {
                        $retDeliverGoodsTypeMsg = '代发';
                    } elseif ($value['deliver_goods_type'] == 3) {
                        $retDeliverGoodsTypeMsg = '送货';
                    }
                    $retTransportTypeMsg = '';
                    if ($value['return_transport_type'] == 1) {
                        $retTransportTypeMsg = '空运';
                    } elseif ($value['return_transport_type'] == 2) {
                        $retTransportTypeMsg = '海运';
                    } elseif ($value['return_transport_type'] == 3) {
                        $retTransportTypeMsg = '铁路';
                    } elseif ($value['return_transport_type'] == 4) {
                        $retTransportTypeMsg = '公路';
                    }
                    $moneyFlag = empty($value['money_flag']) ? '收款' : '退款';
                    $outStatusMsg = ($value['out_status'] == 2) ? '全部出库' : '待出库';
                    $inStatusMsg = ($value['in_status'] == 2) ? '全部入库' : '待入库';
                    $payStatus = ($value['pay_status'] == 2) ? '全部付款' : '待付款';
                    for ($i = 0; $i < $forNum; $i++) {
                        //换货商品
                        $barGoodsVal = $value['barter_goods_detail_info'][$i] ?? [];
                        $retGoodsVal = $value['return_goods_detail_info'][$i] ?? [];
                        $retLogisticInfo = $retLogisticsList[$i] ?? [];
                        $barLogisticInfo = $barLogisticsList[$i] ?? [];
                        $condition = [];
                        $barSerialList = '';
                        if (!empty($barGoodsVal['type_id'])) {
                            $barOutSerialList = BarterOutSerialNumberModel::select('serial_number')
                                ->where([
                                    'delete_flag' => 0,
                                    'main_id' => $value['main_id'],
                                    'type_id' => $barGoodsVal['type_id'],
                                    'barter_id' => $value['barter_id']
                                ])->get()->toArray();
                            if (!empty($barOutSerialList)) {
                                $barSerialList = join(',', array_column($barOutSerialList, 'serial_number'));
                            }
                        }
                        $retSerialList = '';
                        if (!empty($retGoodsVal['type_id'])) {
                            $retOutSerialList = SalesOutSerialNumberModel::select('serial_number')
                                ->where([
                                    'delete_flag' => 0,
                                    'main_id' => $value['main_id'],
                                    'type_id' => $retGoodsVal['type_id'],
                                    'return_id' => $value['return_id']
                                ])
                                ->get()->toArray();
                            if (!empty($retOutSerialList)) {
                                $retSerialList = join(',', array_column($retOutSerialList, 'serial_number'));
                            }
                        }
                        $retGoodsName = !empty($retGoodsVal['materiel_name']) ? $retGoodsVal['materiel_name'] . '（' . $retGoodsVal['materiel_name'] . '）' : '';
                        $retStorageName = !empty($retGoodsVal['storage_name']) ? $retGoodsVal['storage_name'] . '/' . $retGoodsVal['shelf_name'] . '/' . $retGoodsVal['location_name'] : '';
                        $barGoodsName = !empty($barGoodsVal['materiel_name']) ? $barGoodsVal['materiel_name'] . '（' . $barGoodsVal['materiel_name'] . '）' : '';
                        $barStorageName = !empty($barGoodsVal['storage_name']) ? $barGoodsVal['storage_name'] . '/' . $barGoodsVal['shelf_name'] . '/' . $barGoodsVal['location_name'] : '';
                        $list[] = [
                            'nickname' => $value['nickname'],//操作人昵称
                            'username' => $value['username'],//操作人账号
                            'customer_name' => $value['customer_name'], //客户
                            'approver_name' => $value['approver_name'], //审批人
                            'barter_date' => $value['barter_date'], //创建时间
                            'out_order_no' => $value['out_order_no'], //外部订单
                            'spread_price' => $value['spread_price'], //差价（元）
                            'payment_type' => $moneyFlag, //付款类型
                            'expend' => $value['expend'], //付款金额（元）
                            'pay_status' => $payStatus, //付款状态
                            'collection_account' => $value['collection_account'], //付款账号
                            'collection_time' => $value['collection_time'], //付款时间
                            'general_remarks' => $value['general_remarks'],//总备注
                            'ret_order_no' => $value['return_order_no'], //退货-订单号
                            'in_status' => $inStatusMsg, //入库状态
                            'ret_deliver_goods_type' => $retDeliverGoodsTypeMsg,//发货方式
                            'ret_transport_type' => $retTransportTypeMsg,//运输方式
                            'ret_consignee' => $value['return_consignee'] . '/' . $value['return_consignee_phone'] . '/' . $value['return_consignee_address'],//收货人
                            'ret_deliver_time' => $value['return_deliver_time'],//发货时间
                            'ret_remark' => $value['return_remark'] ?? '',//备注信息
                            'ret_logistics_data' => '退货物流',//物流信息
                            'ret_logistics_info' => $retLogisticInfo['logistics_company'] ?? '',//物流公司
                            'ret_logistics_order_no' => $retLogisticInfo['logistics_order_no'] ?? '',//物流单号
                            'ret_goods_data' => '退货商品',//商品信息
                            'ret_goods_name' => $retGoodsName,//商品名称（名称/编号）
                            'ret_storage_name' => $retStorageName,//入库仓库（仓库/货架/库位）
                            'ret_cost_price' => $retGoodsVal['cost_price'] ?? '',//成本价格（元）
                            'ret_price' => $retGoodsVal['price'] ?? '',//销售价格（元）
                            'ret_num' => isset($retGoodsVal['num']) ? $retGoodsVal['num'] . $retGoodsVal['materiel_unit'] : '',//操作数量
                            'in_num' => isset($retGoodsVal['int_num']) ? $retGoodsVal['int_num'] . $retGoodsVal['materiel_unit'] : '',//入库数量
                            'ret_serial_no' => $retSerialList,//序列号
                            'ret_goods_remark' => $retGoodsVal['remark'] ?? '',//备注
                            'ret_total_money' => $retGoodsVal['return_money'] ?? '',//退货总金额（元）
                            'bar_order_no' => $value['order_no'],//换货-订单号
                            'bar_out_status' => $outStatusMsg,//出库状态
                            'bar_deliver_type' => $barDeliverGoodsTypeMsg,//发货方式
                            'bar_transport_type' => $barTransportTypeMsg,//运输方式
                            'bar_consignee' => $value['consignee'] . '/' . $value['phone'] . '/' . $value['address'],//收货人
                            'bar_deliver_time' => $value['deliver_time'],//发货时间
                            'bar_remark' => $value['remark'],//备注信息
                            'bar_logistics_data' => '换货物流',//物流信息
                            'bar_logistics_info' => $barLogisticInfo['logistics_company'] ?? '',//物流公司
                            'bar_logistics_order_no' => $barLogisticInfo['logistics_order_no'] ?? '',//物流单号
                            'bar_goods_data' => '换货商品',//商品信息
                            'bar_goods_name' => $barGoodsName,//商品名称（名称/编号）
                            'bar_storage_name' => $barStorageName,//入库仓库（仓库/货架/库位）
                            'bar_cost_price' => $barGoodsVal['cost_price'] ?? '',//成本价格（元）
                            'bar_price' => $barGoodsVal['price'] ?? '',//销售价格（元）
                            'bar_num' => isset($barGoodsVal['num']) ? $barGoodsVal['num'] . $barGoodsVal['materiel_unit'] : '',//操作数量
                            'out_num' => isset($barGoodsVal['out_num']) ? $barGoodsVal['out_num'] . $barGoodsVal['materiel_unit'] : '',//出库数量
                            'bar_serial_no' => $barSerialList ?? '',//序列号
                            'bar_goods_remark' => $barGoodsVal['remark'] ?? '',//备注
                            'bar_total_money' => $value['money'] ?? '',//换货总金额（元）
                        ];

                    }
                } else {
                    $goodsNum[] = 1;
                    $list[] = [
                        'nickname' => $value['nickname'],//操作人昵称
                        'username' => $value['username'],//操作人账号
                        'customer_name' => $value['customer_name'], //客户
                        'approver_name' => $value['approver_name'], //审批人
                        'barter_date' => $value['barter_date'], //创建时间
                        'out_order_no' => $value['out_order_no'], //外部订单
                        'spread_price' => $value['spread_price'], //差价（元）
                        'payment_type' => $moneyFlag, //付款类型
                        'expend' => $value['expend'], //付款金额（元）
                        'pay_status' => $payStatus, //付款状态
                        'collection_account' => $value['collection_account'], //付款账号
                        'collection_time' => $value['collection_time'], //付款时间
                        'general_remarks' => $value['general_remarks'],//总备注
                        'ret_order_no' => $value['return_order_no'], //退货-订单号
                        'in_status' => $inStatusMsg, //入库状态
                        'ret_deliver_goods_type' => $retDeliverGoodsTypeMsg,//发货方式
                        'ret_transport_type' => $retTransportTypeMsg,//运输方式
                        'ret_consignee' => $value['return_consignee'] . '/' . $value['return_consignee_phone'] . '/' . $value['return_consignee_address'],//收货人
                        'ret_deliver_time' => $value['return_deliver_time'],//发货时间
                        'ret_remark' => $value['return_remark'] ?? '',//备注信息
                        'ret_logistics_data' => '退货物流',//物流信息
                        'ret_logistics_info' => '',//物流公司
                        'ret_logistics_order_no' => '',//物流单号
                        'ret_goods_data' => '退货商品',//商品信息
                        'ret_goods_name' => '',//商品名称（名称/编号）
                        'ret_storage_name' => '',//入库仓库（仓库/货架/库位）
                        'ret_cost_price' => '',//成本价格（元）
                        'ret_price' => '',//销售价格（元）
                        'ret_num' => '',//操作数量
                        'in_num' => '',//入库数量
                        'ret_serial_no' => '',//序列号
                        'ret_goods_remark' => '',//备注
                        'ret_total_money' => '',//退货总金额（元）
                        'bar_order_no' => $value['order_no'],//换货-订单号
                        'bar_out_status' => $outStatusMsg,//出库状态
                        'bar_deliver_type' => $barDeliverGoodsTypeMsg,//发货方式
                        'bar_transport_type' => $barTransportTypeMsg,//运输方式
                        'bar_consignee' => $value['consignee'] . '/' . $value['phone'] . '/' . $value['address'],//收货人
                        'bar_deliver_time' => $value['deliver_time'],//发货时间
                        'bar_remark' => $value['remark'],//备注信息
                        'bar_logistics_data' => '换货物流',//物流信息
                        'bar_logistics_info' => '',//物流公司
                        'bar_logistics_order_no' => '',//物流单号
                        'bar_goods_data' => '换货商品',//商品信息
                        'bar_goods_name' => '',//商品名称（名称/编号）
                        'bar_storage_name' => '',//入库仓库（仓库/货架/库位）
                        'bar_cost_price' => '',//成本价格（元）
                        'bar_price' => '',//销售价格（元）
                        'bar_num' => '',//操作数量
                        'out_num' => '',//入库数量
                        'bar_serial_no' => '',//序列号
                        'bar_goods_remark' => '',//备注
                        'bar_total_money' => '',//换货总金额（元）
                    ];
                }
            }
        }
        $cell = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'X', 'AG',
            'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AR', 'BA'
        ];//需要合并的单元格

        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;

                $sheet->setCellValue('A' . $key, $value['nickname'])
                    ->setCellValue('B' . $key, $value['username'])
                    ->setCellValue('C' . $key, $value['customer_name'])
                    ->setCellValue('D' . $key, $value['approver_name'])
                    ->setCellValue('E' . $key, $value['barter_date'])
                    ->setCellValue('F' . $key, $value['out_order_no'])
                    ->setCellValue('G' . $key, $value['spread_price'])
                    ->setCellValue('H' . $key, $value['payment_type'])
                    ->setCellValue('I' . $key, $value['expend'])
                    ->setCellValue('J' . $key, $value['pay_status'])
                    ->setCellValue('K' . $key, $value['collection_account'])
                    ->setCellValue('L' . $key, $value['collection_time'])
                    ->setCellValue('M' . $key, $value['general_remarks'])
                    ->setCellValue('N' . $key, $value['ret_order_no'])
                    ->setCellValue('O' . $key, $value['in_status'])
                    ->setCellValue('P' . $key, $value['ret_consignee'])
                    ->setCellValue('Q' . $key, $value['ret_deliver_goods_type'])
                    ->setCellValue('R' . $key, $value['ret_transport_type'])
                    ->setCellValue('S' . $key, $value['ret_deliver_time'])
                    ->setCellValue('T' . $key, $value['ret_remark'])
                    ->setCellValue('U' . $key, $value['ret_logistics_data'])
                    ->setCellValue('V' . $key, $value['ret_logistics_info'])
                    ->setCellValue('W' . $key, $value['ret_logistics_order_no'])
                    ->setCellValue('X' . $key, $value['ret_goods_data'])
                    ->setCellValue('Y' . $key, $value['ret_goods_name'])
                    ->setCellValue('Z' . $key, $value['ret_storage_name'])
                    ->setCellValue('AA' . $key, $value['ret_cost_price'])
                    ->setCellValue('AB' . $key, $value['ret_price'])
                    ->setCellValue('AC' . $key, $value['ret_num'])
                    ->setCellValue('AD' . $key, $value['in_num'])
                    ->setCellValue('AE' . $key, $value['ret_serial_no'])
                    ->setCellValue('AF' . $key, $value['ret_goods_remark'])
                    ->setCellValue('AG' . $key, $value['ret_total_money'])
                    ->setCellValue('AH' . $key, $value['bar_order_no'])
                    ->setCellValue('AI' . $key, $value['bar_out_status'])
                    ->setCellValue('AJ' . $key, $value['bar_consignee'])
                    ->setCellValue('AK' . $key, $value['bar_deliver_type'])
                    ->setCellValue('AL' . $key, $value['bar_transport_type'])
                    ->setCellValue('AM' . $key, $value['bar_deliver_time'])
                    ->setCellValue('AN' . $key, $value['bar_remark'])
                    ->setCellValue('AO' . $key, $value['bar_logistics_data'])
                    ->setCellValue('AP' . $key, $value['bar_logistics_info'])
                    ->setCellValue('AQ' . $key, $value['bar_logistics_order_no'])
                    ->setCellValue('AR' . $key, $value['bar_goods_data'])
                    ->setCellValue('AS' . $key, $value['bar_goods_name'])
                    ->setCellValue('AT' . $key, $value['bar_storage_name'])
                    ->setCellValue('AU' . $key, $value['bar_cost_price'])
                    ->setCellValue('AV' . $key, $value['bar_price'])
                    ->setCellValue('AW' . $key, $value['bar_num'])
                    ->setCellValue('AX' . $key, $value['out_num'])
                    ->setCellValue('AY' . $key, $value['bar_serial_no'])
                    ->setCellValue('AZ' . $key, $value['bar_goods_remark'])
                    ->setCellValue('BA' . $key, $value['bar_total_money']);
            }
        }

        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log('退换货记录表', $data, '导出共' . count($exportData) . '条');
        return self::downloadExcel($spreadsheet, '退换货记录表_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     *
     * 公众号绑定微信
     * @param Request $request
     * @return JsonResponse
     */
    public function gzWechatBind(Request $request)
    {
        $bool = false;
        $from = 'pc';
        $data = $request->all();
        $userData = [];
        if (strpos($request->userId, '-') !== false) {
            $userData = explode('-', $request->userId);
            $request->userId = $userData[0];
        }
        $gzUserInfo = [];
        $adminInfo = AdminModel::where('id', $request->userId)->first();
        $gzInfo = json_decode(LoginService::getGzOpenId($request->code), true);
        if (isset($userData[1])) $from = $userData[1];
        if (!empty($adminInfo['gz_openid'])) {
            return redirect($request->getSchemeAndHttpHost() . '/gzh/bindSuccessful.html?from=' . $from);
        }
        if (empty($adminInfo['gz_openid']) && !empty($request->code)) {
            $gzUserInfo = json_decode(LoginService::getGzUserInfo($gzInfo), true);
            $bool = AdminModel::where('id', $adminInfo['id'])->update([
                'gz_openid' => $gzInfo['openid'],
                'wx_nickname' => $gzUserInfo['nickname']
            ]);
        }
        $logData = '微信绑定成功！';
        $gzInfo['user_id'] = $adminInfo['id'];
        $gzInfo['main_id'] = !empty($adminInfo['parent_id']) ? $adminInfo['parent_id'] : $request->userId;
        //CommonService::log('绑定公众号微信', array_merge($gzInfo, $gzUserInfo), $logData);
        if ($bool) {
            return redirect($request->getSchemeAndHttpHost() . '/gzh/bindSuccessful.html?from=' . $from);
        } else {
            return redirect($request->getSchemeAndHttpHost() . '/gzh/bindFailed.html?from=' . $from);
        }
    }

    /**
     *已售商品统计-客户购买详情列表导出
     * Author: zlf
     * Created at 2023/12/29 11:19
     * @param Request $request
     * @return JsonResponse
     */
    public function sellGoodsBuyCustomerExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'sell_goods_id' => 'required|integer',//销售商品id
        ], [

            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'sell_goods_id.required' => '`销售商品id`缺失',
            'sell_goods_id.integer' => '`销售商品id`只能为数字',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code' => 1, 'msg' => $info, 'data' => '']);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }
        $condition = [];
        $token = $request->header('token');
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where('id', $tokenInfo['userId'])->first();
        if (empty($adminInfo)) {
            return response()->json(['code' => 1, 'msg' => '账号异常', 'data' => '']);
        }
        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.created_at', '>=', $startTime];
            $condition[] = ['a.created_at', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['a.created_at', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.created_at', '<=', $endTime];
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $exportData = StatisticsService::sellGoodsBuyCustomerList($condition, $data);
        /* echo "<pre>";
        var_dump($exportData);die;*/
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $title = '已售商品统计-客户购买详情统计表';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle($title);
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "F");
        $sheet->setCellValue('A1', '商品名称')
            ->setCellValue('B1', '公司名称')
            ->setCellValue('C1', '客户名称')
            ->setCellValue('D1', '购买数量')
            ->setCellValue('E1', '小计')
            ->setCellValue('F1', '总数量');
        $materielLine = [];
        $list = [];
        if (!empty($exportData)) {
            foreach ($exportData['list'] as $fVal) {
                $materielLine[] = count($fVal['name_list']);
                foreach ($fVal['name_list'] as $sVal) {
                    $list[] = [
                        'goods_name' => $exportData['goods_name'],
                        'compony_name' => $fVal['compony_name'],
                        'name' => $sVal['customer_name'],
                        'num' => $sVal['num'],
                        'compony_subtotal' => $fVal['compony_subtotal'],
                        'total_num' => $exportData['total_num'],
                    ];
                }
            }
        }
        $cell01 = ['B', 'E'];//需要合并的单元格
        $cell02 = ['A', 'F'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell01 as $item) {
                $start = 2;
                foreach ($materielLine as $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
            foreach ($cell02 as $item) {
                $start = 2;
                $end = ($start + count($list)) - 1;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['goods_name'])
                    ->setCellValue('B' . $key, $value['compony_name'])
                    ->setCellValue('C' . $key, $value['name'])
                    ->setCellValue('D' . $key, $value['num'])
                    ->setCellValue('E' . $key, $value['compony_subtotal'])
                    ->setCellValue('F' . $key, $value['total_num']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     *客户销售统计-客户统计详情导出
     * Author: zlf
     * Created at 2023/12/29 11:19
     * @param Request $request
     * @return JsonResponse
     */
    public function statementCustomerDetailExport(Request $request)
    {
        $token = $request->header('token');
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where([
            'id' => $tokenInfo['userId'],
            'delete_flag' => 0
        ])->first();
        if (empty($adminInfo)) {
            return response()->json(['code' => 1, 'msg' => '账号异常', 'data' => '']);
        }
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'customer_id' => 'required|integer',//供应商或客户id
            'customer_name' => 'required',//供应商或客户id
            'type' => [
                'required',
                Rule::In([0, 1]),
            ],//统计类型;0:采购;1:销售;
        ], [
            'customer_id.required' => '`供应商或客户id`缺失',
            'customer_id.integer' => '`供应商或客户id`只能为数字',
            'customer_name.required' => '`供应商或客户名称`缺失',
            'type.required' => '`统计类型`缺失',
            'type.In' => '`统计类型`不合法',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }
        /* $tokenInfo['mainUserId'] = 9604;
        $tokenInfo['userId'] = 9604;*/
        if ($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $condition['main_id'] = ['EQ' => $tokenInfo['mainUserId']];
        } else {
            $condition['user_id'] = ['EQ' => $tokenInfo['userId']];
        }

        $exportData = StatisticsService::statementCustomerDetail($data, $condition);
        /*   echo "<pre>";
         var_dump($exportData);die;*/
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $title = '已售商品统计-客户购买详情统计表';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "I");
        $sheet->setCellValue('A1', '订单号');
        if (!empty($data['type'])) {
            $title = '客户销售统计详情表';
            $sheet->setCellValue('B1', '销售（件）')
                ->setCellValue('C1', '总金额（元）')
                ->setCellValue('D1', '欠款（元）')
                ->setCellValue('E1', '销售时间')
                ->setCellValue('F1', '总利润')
                ->setCellValue('G1', '总欠款')
                ->setCellValue('H1', '总销售')
                ->setCellValue('I1', '总销售额');
        } else {
            $title = '客户采购统计详情表';
            $sheet->setCellValue('B1', '采购数量')
                ->setCellValue('C1', '采购总金额（元）')
                ->setCellValue('D1', '欠款（元）')
                ->setCellValue('E1', '日期')
                ->setCellValue('F1', '总利润')
                ->setCellValue('G1', '总欠款')
                ->setCellValue('H1', '总采购')
                ->setCellValue('I1', '总采购额');
        }
        $sheet->setTitle($title);
        $materielLine = [];
        $list = [];
        if (!empty($exportData)) {
            $materielLine[] = count($exportData['list_info']);
            foreach ($exportData['list_info'] as $val) {
                $list[] = [
                    'order_no' => !empty($data['type']) ? $val['order_no'] : $val['purchase_no'],
                    'num' => !empty($data['type']) ? $val['sell_num'] : $val['materiel_num'],
                    'money' => $val['final_money'],
                    'debt' => $val['debt'],
                    'time' => !empty($data['type']) ? $val['sell_date'] : $val['purchase_time'],
                    'total_income' => $exportData['total_income'],
                    'total_debt' => $exportData['sell_debt'],
                    'total_num' => !empty($data['type']) ? $exportData['sell_total_num'] : $exportData['materiel_total_num'],
                    'total_money' => !empty($data['type']) ? $exportData['sell_final_money'] : $exportData['materiel_total_money'],
                ];
            }
        }
        $cell = ['F', 'G', 'H', 'I'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['order_no'])
                    ->setCellValue('B' . $key, $value['num'])
                    ->setCellValue('C' . $key, $value['money'])
                    ->setCellValue('D' . $key, $value['debt'])
                    ->setCellValue('E' . $key, $value['time'])
                    ->setCellValue('F' . $key, $value['total_income'])
                    ->setCellValue('G' . $key, $value['total_debt'])
                    ->setCellValue('H' . $key, $value['total_num'])
                    ->setCellValue('I' . $key, $value['total_money']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     *客户销售商品统计导出
     * Author: zlf
     * Created at 2023/12/29 11:19
     * @param Request $request
     * @return JsonResponse
     */
    public function clientGoodsSoldExport(Request $request)
    {
        $token = $request->header('token');
        //$token ='18579 CkR4Nu/L12xwZixL/nYUYBfzEfrEKfGDe943pOd7YLDWRorkUHFG0D7U3alE9fJf1AXKBADrRICxXrME5bhF99vpeiWxagVpfeCoHS80frVOjW9Kd14GfzN/oIhRXPid1WrFNAC2M2noai5hyKDRHCQHljf+1vaLfym8g2Q9r7C1714tIYZbkBahl9QFF+ncss0dODI56Ps9vw3VLDbXSWQqV3nn2sP2d8DnIYcbtDnrvEIW36OIlXW/0npzRimx2tv4/GcuoA1cLOeloY3Y/Z0LjJLEglo8zyOFssfmcYq3BKAYg40OxwJiNEW129Z5UPW3DKkkmZZA6h77ekWOMg==';
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where([
            'id' => $tokenInfo['userId'],
            'delete_flag' => 0
        ])->first();
        if (empty($adminInfo)) {
            return response()->json(['code' => 1, 'msg' => '账号异常', 'data' => '']);
        }
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'customer_id' => 'required|integer',//供应商或客户id
        ], [
            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'customer_id.required' => '`供应商或客户id`缺失',
            'customer_id.integer' => '`供应商或客户id`只能为数字',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];
        if ($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $condition['a.main_id'] = ['EQ' => $tokenInfo['userId']];
        } else {
            $condition['a.user_id'] = ['EQ' => $tokenInfo['userId']];
        }

        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
            $condition[] = ['sell_date', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '<=', $endTime];
        }

        $keyword = empty($request->keyword) ? '' : $request->keyword;
        $exportData = StatisticsService::sellGoodsList($condition, $data, $keyword);
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $title = '客户销售商品统计表';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $spreadsheet = SELF::spreadsheetCommonMethod($spreadsheet, "A", "E");
        $sheet->setCellValue('A1', '客户名称')
            ->setCellValue('B1', '总销售金额（元）')
            ->setCellValue('C1', '商品名称/类型')
            ->setCellValue('D1', '商品数量')
            ->setCellValue('E1', '销售金额');
        $sheet->setTitle($title);
        $materielLine = [];
        $list = [];
        if (!empty($exportData)) {
            $materielLine[] = count($exportData['sell_goods_list']);
            foreach ($exportData['sell_goods_list'] as $val) {
                $list[] = [
                    'customer_name' => $exportData['customer_name'],
                    'sell_total_money' => $exportData['sell_final_money'],
                    'materiel_name' => $val['materiel_name'] . '/' . $val['type_name'],
                    'total_num' => $val['total_num'],
                    'total_cost' => $val['cost_price']
                ];
            }
        }
        $cell = ['A', 'B'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['customer_name'])
                    ->setCellValue('B' . $key, $value['sell_total_money'])
                    ->setCellValue('C' . $key, $value['materiel_name'])
                    ->setCellValue('D' . $key, $value['total_num'])
                    ->setCellValue('E' . $key, $value['total_cost']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData['sell_goods_list']) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     *客户采购商品统计导出
     * Author: zlf
     * Created at 2024/1/6 11:10
     * @param Request $request
     * @return void
     */
    public function clientGoodsPurchaseExport(Request $request)
    {

        $token = $request->header('token');
        //$token = '9604 O5fgOdbgkCfd0iYnzFJuicm+/FYltKt5nAb2n3fZTWctM1h5/ZkOejPF/jrQj2pO1GmwoTcMMJ/GfKOjih6vJX77fwZQDoG9nNNZmSxNCYL4ALnMr1CvIc320ivz1ZEFuEosDNgsXmDktleGFQfPwp6CPmY9LHErWArQaRI1WeST69u0HV+mUG5xncyCUc7cOtbCkpk4shD2Le1jaZEpCt+3+EtoEQIUjSh+cI6Iy+qPpcHDd4lxgvDHHk7FiuAm188zjze8VCRtKojGh2oX0TIIeweVk5zEGH+RQLBGWXPVnFfoakUmgcscoAWcAyaTdg95lS1bJJOMypzuRWuI5Q==';
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where([
            'id' => $tokenInfo['userId'],
            'delete_flag' => 0
        ])->first();
        if (empty($adminInfo)) {
            return response()->json(['code' => 1, 'msg' => '账号异常', 'data' => '']);
        }
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'customer_id' => 'required|integer',//供应商或客户id
        ], [
            'start_time.date_format' => '开始日期格式有误，例：' . date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：' . date('Y-m-d'),
            'customer_id.required' => '`供应商或客户id`缺失',
            'customer_id.integer' => '`供应商或客户id`只能为数字',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }

        if (!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0] . ' 00:00:00');
        }

        if (!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0] . ' 23:59:59');
        }

        $condition = [];
        if ($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $condition['b.main_id'] = ['EQ' => $tokenInfo['userId']];
        } else {
            $condition['b.user_id'] = ['EQ' => $tokenInfo['userId']];
        }

        if (!empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
            $condition[] = ['sell_date', '<=', $endTime];
        } else if (!empty($startTime) && empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
        } else if (empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '<=', $endTime];
        }
        $keyword = empty($request->keyword) ? '' : $request->keyword;
        $exportData = StatisticsService::statisticsPurchasedGoods($condition, $data, $keyword);
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $title = '客户采购商品统计表';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $spreadsheet = self::spreadsheetCommonMethod($spreadsheet, "A", "F");
        $sheet->setCellValue('A1', '客户名称')
            ->setCellValue('B1', '总数量')
            ->setCellValue('C1', '总成本（元）')
            ->setCellValue('D1', '商品名称/类型')
            ->setCellValue('E1', '商品数量')
            ->setCellValue('F1', '商品成本');
        $sheet->setTitle($title);
        $materielLine = [];
        $list = [];
        if (!empty($exportData)) {
            $materielLine[] = count($exportData['materiel_content_info']);
            foreach ($exportData['materiel_content_info'] as $val) {
                $list[] = [
                    'customer_name' => $exportData['customer_name'],
                    'total_amount' => $exportData['total_amount'],
                    'total_price' => $exportData['total_price'],
                    'materiel_name' => $val['materiel_name'] . '/' . $val['type_name'],
                    'total_num' => $val['num'],
                    'total_cost' => $val['total_unit_price']
                ];
            }
        }
        $cell = ['A', 'B'];//需要合并的单元格
        if (!empty($list)) {
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $value) {
                    $end = ($start + $value) - 1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['customer_name'])
                    ->setCellValue('B' . $key, $value['total_amount'])
                    ->setCellValue('C' . $key, $value['total_price'])
                    ->setCellValue('D' . $key, $value['materiel_name'])
                    ->setCellValue('E' . $key, $value['total_num'])
                    ->setCellValue('F' . $key, $value['total_cost']);
            }
        }
        $data['main_id'] = $tokenInfo['mainUserId'];
        $data['user_id'] = $tokenInfo['userId'];
        CommonService::log($title, $data, '导出共' . count($exportData['materiel_content_info']) . '条');
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');

    }

    public function animalInOutExport(Request $request): ?JsonResponse
    {
        $data = $request->all();
        $token = $request->header('token');
        //$token = '9604 i3t+38uvfuD3x/ibm6joozC6teh2e9Q3noUQWho4QBxqOe6/O0Ml721aK+W8vJweRwdubPSQQB8DeOICHyAP69trrg/M0xUW+npb1GZW2runkev1Nlv7bCJK75vRK9lhB3oNlxD9qZMjuhivxX0pN6fgj8dLJJ1XscBBzFngQcxfacq9n0+P1ab0LkeHSSzCEL3f2OFHVpmJ9SF1LvbOy+IZttGV8bK6AN0gLFU7KjMeKfhgb2K4In/OnBYaRV1ssT5h7o6JB/mDNGSokteFtMariHNKBq0M9B3oaLCY/1zl4EhYbmylIsff0WmYyZXi1+EQYD44KrIeIdfoGuWU2w==';
        if (empty($token)) {
            return response()->json(['code' => 1, 'msg' => 'token验证失败，请重新登录', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!empty($tokenInfo['code'])) return fail('', $tokenInfo['msg']);
        $adminInfo = AdminModel::where([
            'id' => $tokenInfo['userId'],
            'delete_flag' => 0
        ])->first();
        if (empty($adminInfo)) {
            return response()->json(['code' => 1, 'msg' => '账号异常', 'data' => '']);
        }
        $condition = [];
        if ($tokenInfo['mainUserId'] === $tokenInfo['userId']) {
            $condition['d.main_id'] = ['EQ' => $tokenInfo['userId']];
        } else {
            $condition['d.user_id'] = ['EQ' => $tokenInfo['userId']];
        }
        $list = LocationLogService::getStorageAnimalList($data, $condition, 1);
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $title = '动物（' . !empty($data['type']) ? '入库' : '出库' . '）表';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $spreadsheet = self::spreadsheetCommonMethod($spreadsheet, "A", "M");
        $sheet->setCellValue('A1', '仓库名称')
            ->setCellValue('B1', '柜子名称')
            ->setCellValue('C1', '盒子名称')
            ->setCellValue('D1', '动物名称（性格）')
            ->setCellValue('E1', '基因')
            ->setCellValue('F1', '克重')
            ->setCellValue('G1', '数量')
            ->setCellValue('H1', '入库价格')
            ->setCellValue('I1', '出库销售价格')
            ->setCellValue('J1', '备注')
            ->setCellValue('K1', '标签')
            ->setCellValue('L1', '操作')
            ->setCellValue('M1', '操作时间');
        $sheet->setTitle($title);
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $tagContent = OutAnimalTagModel::query()
                    ->where('id', $value['out_tag_id'])
                    ->value('tag_content') ?? '';
                $key += 2;
                $sheet->setCellValue('A' . $key, $value['storage_name'])
                    ->setCellValue('B' . $key, $value['shelf_name'])
                    ->setCellValue('C' . $key, $value['location_name'])
                    ->setCellValue('D' . $key, $value['animal_name'] . '（' . $value['sex'] . '）')
                    ->setCellValue('E' . $key, $value['gene'])
                    ->setCellValue('F' . $key, $value['gram_weight'])
                    ->setCellValue('G' . $key, $value['num'])
                    ->setCellValue('H' . $key, $value['in_price'])
                    ->setCellValue('I' . $key, $value['sell_price'])
                    ->setCellValue('J' . $key, $value['remark'])
                    ->setCellValue('K' . $key, $tagContent)
                    ->setCellValue('L' . $key, !empty($request->type) ? '入库' : '出库')
                    ->setCellValue('M' . $key, $value['type_time']);
            }
        }
        return SELF::downloadExcel($spreadsheet, $title . '_' . date('Y-m-d'), 'Xlsx');
    }

    /**
     *编辑表格样式公用方法
     * Author: zlf
     * Created at 2023/12/30 11:35
     * @param $spreadsheet
     * @param $start
     * @param $end
     * @return void
     */
    private static function spreadsheetCommonMethod($spreadsheet, $start, $end)
    {
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle($start . '1:' . $end . '1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
        $spreadsheet->getActiveSheet()->getStyle($start . ':' . $end)->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle($start . ':' . $end)->getAlignment()->setVertical('center');

        for ($column = $start; $column != $end; $column++) {
            //设置列宽
            $spreadsheet->getActiveSheet()->getColumnDimension($column)->setWidth(50);
            //设置自动换行
            $spreadsheet->getActiveSheet()->getStyle($column)->getAlignment()->setWrapText(true);
        }
        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension($end)->setWidth(50);
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle($end)->getAlignment()->setWrapText(true);
        return $spreadsheet;
    }

    /**
     *查询客服与打印机购买链接信息
     * Author: zlf
     * Created at 2024/2/28 13:58
     * @return void
     */
    public function getCustomerServicerPrinterUrlInfo()
    {
        return success(CustomerServiceModel::query()->select(
            'id',
            'corpid',
            'service_url',
            'service_phone',
            'tb_url'
        )->orderBy('id', 'desc')->first() ?? []);
    }

    /**
     *识别仓库货架库位码
     * Author: zlf
     * Created at 2024/4/28 10:15
     * @return void
     */
    public function getIdentifyStorageCode(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'id' => 'required|integer',//供应商或客户id
            'type' => ['required', Rule::in(['warehouse', 'location', 'shelf'])]
        ], [
            'id.required' => '参数主键`id`缺失',
            'id.integer' => '参数主键`id`只能为数字',
            'type.required' => '参数类型`type`缺失',
            'type.in' => '参数类型`type`值有误！',
        ]);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }
        $userInfo = [];
        $token = $request->header('token');
        if (!empty($token)) {
            $tokenInfo = self::checkToken($token);
            $userInfo['user_id'] = $tokenInfo['userId'];
            $userInfo['main_id'] = $tokenInfo['mainUserId'];
        }
        return StorageManageService::getIdentifyStorageCode($userInfo, $data);
    }

    /**
     *申请表详情H5
     * Author: zlf
     * Created at 2024/6/5 14:28
     * @param Request $request
     * @return JsonResponse
     */
    public function getApplicationFormInfo(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'id' => 'required|integer',//申请单id
        ], [
            'id.required' => '参数主键`id`缺失',
            'id.integer' => '参数主键`id`只能为数字',
        ]);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return fail('', $info);
        }
        return success(ApplicationFormService::ApplicationFormInfo($data));
    }

    public function getNoTokenAnimalTagList(Request $request): JsonResponse
    {
        return success(ShelfLocationTagService::tagList($request->all()));
    }

    public function getNoTokenShelfLocationList(Request $request)
    {
        $data = $request->all();
        return success(StorageShelfLocationService::getNoTokenShelfLocaList($data));
    }

    /**
     *导入货架库位
     * Author: zlf
     * Created at 2024/6/25 13:50
     * @param Request $request
     * @return array|JsonResponse|mixed|void
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function shelfLocationImport(Request $request)
    {
        $req = $request->all();
        if (empty($req['storage_id'])) {
            return fail('', '仓库主键，不能为空！');
        }
        $token = $request->header('token');
        if (empty($token)) {
            return response()->json(['code' => 2, 'msg' => 'token不能为空', 'data' => '']);
        }
        $tokenInfo = self::checkToken($token);
        if (!$tokenInfo) return $tokenInfo;
        $mainId = $tokenInfo['mainUserId'];
        $files = $request->file("shelf_location_temp");
        $ext = $files->extension();
        if ($files->getSize() > 10 * 1024 * 1024) {
            return response()->json(['code' => 1, 'msg' => '文件大小不能超出10M', 'data' => '']);
        }

        //application/vnd.ms-excel  为xls或xlsx文件类型
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return response()->json(['code' => 1, 'msg' => '必须为excel表格，且必须为xlsx或xls格式！', 'data' => '']);
        }
        if ($ext == 'xls') {
            $reader = IOFactory::createReader('Xls');
        } else if ($ext == 'xlsx') {
            $reader = IOFactory::createReader('Xlsx');
        }
        $path = $files->store(date('Y') . '/' . date('m') . '/' . date('d'));
        $filePath = public_path() . '/uploads/' . $path;
        $objPHPExcel = $reader->load($filePath);
        $sheet = $objPHPExcel->getActiveSheet();
        $excelSheet = $objPHPExcel->getSheet(0);//读取第一个工作表
        $highestRow = $excelSheet->getHighestRow();// 取得总行数
        return Cache::lock($req['storage_id'] . __CLASS__ . __FUNCTION__ . $tokenInfo['userId'], 10)
            ->get(function () use ($req, $highestRow, $objPHPExcel, $tokenInfo, $filePath) {
                // 锁定 10 秒并自动释放...
                return response()->json(self::shelfLocationAction($req, $highestRow, $objPHPExcel, $tokenInfo, $filePath));
            });
    }

    /**
     *货架库位导入公共方法
     * Author: zlf
     * Created at 2024/6/26 10:17
     * @param $req
     * @param $highestRow
     * @param $objPHPExcel
     * @param $tokenInfo
     * @param $filePath
     * @return array|null
     */
    public static function shelfLocationAction($req, $highestRow, $objPHPExcel, $tokenInfo, $filePath): ?array
    {
        $data = [];
        for ($j = 2; $j <= $highestRow; $j++) {
            $n = $j - 2;
            $data[$n] = [
                'name' => $objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue(),
                'sort' => $objPHPExcel->getActiveSheet()->getCell('B' . $j)->getValue(),
                'remark' => $objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue(),
            ];
            if (isset($req['shelf_id']))
                $data[$n]['standard_num'] = $objPHPExcel->getActiveSheet()->getCell('D' . $j)->getValue();
        }
        $time = time();
        if (!empty($data)) {
            DB::beginTransaction();
            try {
                $inData = [];
                $i = 0;
                $j = 0;
                foreach ($data as $key => $val) {
                    if (empty($val['name']))
                        throw new \RuntimeException('货架库位名称必填！');
                    $result = [
                        'main_id' => $tokenInfo['mainUserId'],
                        'storage_id' => $req['storage_id'],
                        'sort' => $val['sort'] ?? 0,
                        'remark' => $val['remark'] ?? '',
                        'created_at' => $time
                    ];

                    if (!empty($req['shelf_id'])) {//创建库位
                        if (StorageShelfLocationModel::query()->where([
                            'status' => 1,
                            'main_id' => $tokenInfo['mainUserId'],
                            'location_name' => $val['name'],
                            'storage_id' => $req['storage_id'],
                            'shelf_id' => $req['shelf_id']
                        ])->exists()) {
                            throw new \RuntimeException('库位名称：' . $val['name'] . '，名称重复，请重新创建！');
                        }
                        if (isset($val['standard_num']))
                            $result['standard_num'] = $val['standard_num'];
                        $result['log_no'] = getNO('storage_shelf_location', 'log_no');
                        $result['location_type'] = 2;
                        $result['shelf_id'] = $req['shelf_id'];
                        $result['location_name'] = $val['name'] ?? '';
                        if (!StorageShelfLocationModel::query()->where([
                            'status' => 1,
                            'main_id' => $tokenInfo['mainUserId'],
                            'shelf_id' => $req['shelf_id'],
                            'storage_id' => $req['storage_id']])
                            ->exists()) {
                            if ($j === 0) {
                                $result['location_type'] = 1;
                            }
                            $j++;
                        }
                    } else {//创建货架
                        $result['shelf_type'] = 2;
                        $result['shelf_name'] = $val['name'] ?? '';
                        if (StorageShelfModel::query()->where([
                            'status' => 1,
                            'main_id' => $tokenInfo['mainUserId'],
                            'shelf_name' => $val['name'],
                            'storage_id' => $req['storage_id']
                        ])->exists()) {
                            throw new \RuntimeException('货架：' . $val['name'] . '，名称重复，请重新创建！');
                        }
                        if (!StorageShelfModel::query()->where([
                            'status' => 1,
                            'main_id' => $tokenInfo['mainUserId'],
                            'storage_id' => $req['storage_id']])
                            ->exists()) {
                            if ($i === 0) {
                                $result['shelf_type'] = 1;
                            }
                            $i++;
                        }
                    }
                    $inData[] = $result;
                }

                if (!empty($req['shelf_id'])) {
                    StorageShelfLocationModel::query()->insert($inData);
                } else {
                    StorageShelfModel::query()->insert($inData);
                }
                $logData = '共导入' . ($highestRow - 1) . '条';
                $data['user_id'] = $tokenInfo['userId'];
                $data['main_id'] = $tokenInfo['mainUserId'];
                CommonService::log('导入货架库位', $data, $logData);
                DB::commit();
                unlink($filePath);
                return ['code' => 0, 'msg' => 'success', 'data' => ''];
            } catch (\Exception $e) {
                DB::rollBack();
                Log::debug('导入货架库位异常' . var_export($e->getMessage(), true));
                return ['code' => 1, 'msg' => '导入失败', 'data' => ''];
            }
        } else {
            return ['code' => 1, 'msg' => '无数据', 'data' => ''];
        }
    }

    /**
     *睡猫相册详情
     * Author: zlf
     * Created at 2024/8/15 14:31
     * @param VerifyIdRequest $request
     * @return JsonResponse
     */
    public static function smAlbumInfo(VerifyIdRequest $request): JsonResponse
    {
        $data = $request->all();
        $info = SmPhotoAlbumModel::query()
            ->where([
                'id' => $data['id']
            ])->first();
        if (!empty($info)) {
            if (!empty($info['title'])) {
                $info['detail'] = $info['title'];
                $info['title'] = mb_substr($info['title'], 0, 30, 'UTF-8');
            }
            $info['label_name'] = CustomTagModel::query()
                ->where('id', $info['label_id'])
                ->value('label_name');
            $info['logo_img'] = '/uploads/logo/img/2023-02-23/default-logo.png?x-oss-process=image/resize,m_fill,w_500,h_400';
            if (!empty($info['goods_img'])) {
                $goodsImgArr = explode(',', $info['goods_img']);
                $info['goods_img'] = $goodsImgArr;
                $info['logo_img'] = $goodsImgArr[0] . '?x-oss-process=image/resize,m_fill,w_500,h_400';
            }
            if (!empty($info['detail_img'])) {
                $detailImgArr = explode(',', $info['detail_img']);
                $info['detail_img'] = $detailImgArr;
            }
            $adminInfo = AdminModel::query()
                ->select('company_name', 'company_logo_img as header_img')
                ->where('id', $info['main_id'])->first();

            $info['header_img'] = !empty($adminInfo['header_img']) ? $adminInfo['header_img'] :
                '/uploads/logo/img/2023-02-23/company_logo_img.png';
            $info['company_name'] = $adminInfo['company_name'];
        }
        return success($info);
    }
}
